PL-300 – Section 29: Part 3 Level 7 – Filter and Value Functions Part 3

216. FILTER

Now, suppose I didn’t want the entirety of the table which all does, all changes the context to the zoom out and getting all of the rows. What if I wanted to go from my starting point which is the context and go even further down?

Let’s take an example. Let’s get back to having our product standard cost and there is a field called promotion key. You can see that the vast majority of the sales have been on promotion key one, 15 million for bikes as opposed to 16 million total sales.

Now, somebody says okay this matrix is fine, it’s great, and obviously you can do it as a graph and so forth. But what I want is a measure that gives me not bikes or clothing or accessories grand total, I want it where the promotion keys equal to one and I don’t want it just to be in a matrix I want it to be a standalone measure that I can use elsewhere. So, what we need is a new measure so remember 15 million is the value we want, which gives us the product standard cost where the promotion key is equal to one.

Now, I’m going to choose where to put it, I’m going to choose to put it in DimProductCategory. Obviously, if I put it into FactInternetSales then I can just use a simple if that we had a look at quite a while earlier. So, I’m going to put it into a different one to show how this measure can be calculated. So, new measure. So, this is the product standard sales cost where the promotion key is equal to one. So, this is our standard sum X so we’re going into the FactInternetSales and we are summing up the product standard cost. So that gets us our new measure which we can put in and find that the answer is currently 16 million. So, what I need to do is filter this table down so it focuses not just on the current context but on the current context for promotion key equals one and we do that using a function called filter. So, what filter does is it filters down a table. So, there’s our table and what we’re going to filter it down by well it’s where the promotion key is equal to one and that’s it. So, it’s like a more complicated sum if you can put these equals or not equals too within this more advance sum ifs. So, here I’m saying give me this table but only where the promotion keys equal to one and then with that reduce table sum X the product standard cost. So, now you can see the total has gone down for bikes from 16 million to 15. So, this is the filter function. It returns a table that represents a filtered down, a subset of another table or expression.

217. CALCULATE

Now, another way of using ALL and FILTER, is using the calculate function.

So, currently, what we’ve got is the sum of this table once it’s been filtered of the product standard cost. And, similarly, with the percentage of total, what we’ve got is the SUMX of all of this table. But, we could, to specify the table, and then put around it a calculate. So, that’s how we’ll basic expression, so this can be quite useful if you have got a standard calculation that you don’t really want to modify. And then, we say, I want you then to modify the calculation based on the ALL of the fact internet sales. So, the advantage of this is that our original table stays the same. It’s only later that we modify by saying I want all of that internet sales. And, you will see that it gives exactly the same figures as before. Similarly, for this product standard cost, for motion key 1, we have modified the table here in this table part. But, if instead, we get rid of this, and we just have sum X of fact internet sales. And then around it, we put a calculate, and say we are going to filter that.

So, what is it that we’re going to filter? The fact internet sales. We’re going to filter that when the promotions key is equal to 1. Again, it gives exactly the same answer. So, let’s have a look at all of those again. So, in our first example, we had a sum X and we took the table the entirety of the fact internet sales and did our calculation on it. Using calculate, we just took the base table as it was, and then, as an afterthought, so, by the way, we want it on all of the fact internet sales table. Similarly, we’ll move, that was filtering out, so that was expanding the context, but we’re filtering in, reducing the context further, using this sum X without the calculate, we have to say our table is filtered down right at the beginning. Using calculate, we’re going to say, we’re going to base it on the fact internet sales, and then at the end – oh, by the way, we want to filter it down so that it is where the promotion key is equal to 1.

So, sometimes, you’ll find calculate makes life a fair bit simpler, and sometimes it’s easier just to get the ALL or the FILTER right at the beginning. But, regardless, if you get the mastery of these three functions, you’ll be able to say, “okay, this is my current context, and I want it to be expanded, or filtered, however, I want, very easily.”

218. ALLEXCEPT

Now, previously we’ve had a look at the all function. All removes all of the context, all of the filters. And we also had a look at the filter, which goes down further. But what if we wanted all except for certain things?

For instance, let’s suppose that the context is we have got a table with the product standard cost. And we had in the categories or we had in the rows the category we’ve got the order date hierarchy. So, I’ll just expand through to the next level. And we’ve also got the promotion key. So, let’s expand through the next level, we’ll just take the year of the order date. So, you can see that the context of this $323 figure is accessories sold in 2007, or ordered in 2007, using the promotion key number two.

Now, what percentage of that is that of all accessories? So, we’re not going for the entirety of the table this time, we’re just going for the entirety of the accessories. So, let’s just make a note of where we are. We have got the category, the order date, and the promotion key. And currently this figure here, $332, is for the context of all of these.

Now, if I was going to say all of fact internet sales, and in fact let’s just do that, let’s just get a new measure in the category table. So, there’s my measure. All of fact internet sales, product standard cost. And I’ll put measure two in there. And change this back to a table, we can see that this new measure is all of the internet sales. So, we still have this £323 figure for the accessories. In fact, I’m going to change it back to a matrix because I like the fact I can see totals. So, there’s our 323 and this gives us the all. Because that’s what we’ve got here. But that removes all of the context. It removes category, it removes auto-date, it removes promotion key, it removes anything else that happens to be there. So, what I want to say is, actually I want to keep order date and promotion key as they are. Or I could say, I want to keep, in this particular case, category as it is. Because I want the total of the category each time. So, I want this 262 thousand figure. So, what I need to change this to, well firstly, all except seems to need the calculate to work correctly. So, I’ve tried putting all except in this part, it doesn’t seem to work, so we need to change this so that it is now all at the end. So, we’ll change this. So, we have CALCULATE at the beginning, and then all of internet fact sales at the end. So, now we can change this so we have all except. So, it’s this bit in red that would now need to change over here. We need to put in the columns that we want to keep, and I want to put in the category colour. So, in the Power BI model I think that’s the English category name or something like that. So, that is the basis of our new measure.

So, let’s go into Power BI and change this measure two measure. So, currently we have the all at the beginning, so we’ll change it so that the all is at the end. A fairly simple change. So, that should result in absolutely no difference. But now, we can put all except instead of the all, and we can add the columns that we want to keep, so in this case it’s the English product category name. So, now you can see that instead of it being the entirety of the table, for bikes it’s the bike figure, and for accessories it’s the accessories figure, that’s in measure two. So, what we can now do, of course, is divide one into the other.

Now, if you had wanted to keep the accessories and the year, for instance, then we could do that as well, we could just add in, in this particular case it was the order date, but if we were to leave it like this it wouldn’t actually make a change to the filter, we need to be a bit more specific. So, I’m going to say keep the year.

So, now you can see that each item is being evaluated against that particular category and year. So all except, this allows you to remove context filters except for certain filters. And it seems to work best if you put a calculate around it and have all except at the end

219. ALLSELECTED

Going back to the all functions, all selected is a really good function for getting a visual total. So, you can see what it says, removes context filters from columns and rows, and the current query by returning all other content filters or explicit filters. What this really means is visual totals.

Now, if you were to click on this function you would get this table and webpage. The table itself, very useful, but it’s a bit unwieldy, so what I’ve done is put it into a spreadsheet, so we can just have a look at it. So, what this table is: it’s a filtered down version of a bigger table. So, it’s not the entirety of the table summated. So, for instance, the reseller sales amount, we have a grand total here of eight hundred seventy-seven thousand dollars.

Now, the entirety of the table is, well, it’s eighty million four hundred and fifty thousand dollars. And we get that by using the all, so that removes all of the filters, regardless of what we’ve put on. All selected gives us a visual total when there is nothing in the brackets. So, if I took column C, you can see it is eight hundred and seventy-seven thousand and seventy-seven dollars, or seven dollars. Here, our visual total is 8-7-7-0-0-7. In other words, it summates what it sees.

Now, if you put anything in the brackets, like for instance, here we’re putting the calendar year, then it’s giving us a visual total for everything except this calendar year. So, here for instance, we’re looking at accessories in the year 2001. So, what this is doing is it’s giving us a visual total for all accessories in all years. So, you can see this total’s 38 thousand. So, it’s giving the current context, but without the filter on the calendar year, and similarly, this one gives us the current context, accessories, 2002, but removes the accessories bit. So, it gives us the visual total for 2002. So, if I summate that, you can see 91 thousand. So, it gives us that in bikes, in accessories and clothing, et cetera. So, let’s have a look at how we can actually use this in Power BI. So, we’ve got here what we were working on in the previous video, and I’m just going to make it a bit simpler, so we have got the product name, and the product standard cost.

So, let’s add a new measure to this. And this new measure is going to be the visual sum of the product standard cost. So, that is equal to the calculate of the sum of the product standard cost. Close bracket, and then we want all selected.

Now, it says we can put in a table name or column name, I’m going to choose not to do so. So, we just close all of those brackets, and we’re done. So, this will give us the total for everything that we can see, and as you can see, the total is seventeen million two hundred and seventy-seven thousand dollars in each and every line.

So, now let’s add in a measure based on the all. So, we have a new measure, and this is going to be the actual sum of product standard cost, and again, this is equal to the calculate of the sum of the product standard cost, but this time, instead of using all selected, we want the entirety of the fact internet sales table. So, lets add this in. Let’s format this again in dollars. And you can see these figures are identical, and you’d expect them to be identical at this stage. So, when would this figure, the visual sum of the product standard cost, be different, which uses all selected with nothing in the brackets, be different to the actual sum, which uses the all? It’s when we don’t take the entirety of the table, but we take a smaller part. And the easiest way to do that is to add in a filter. So, let’s add in a filter. We’re going to do this based on the year of the order date. So, drag this in, and we will say that this is a filter for the year 2007.

Now, let’s have a look at the difference. So, in our product standard cost, we have got items adding up to 5.7 million. Our visual sum just shows what we can see, and what we can see is items adding up to 5.7 million. The actual sum is based on the totality of the table. So, it’s based on the all, as opposed to the all selected, and the totality of the table remains at 17 million, regardless of how many filters we put on this. So, let’s take this one step further. Let’s add in, again, two more measures. We’ll have at the percent of actual sum, so this is the sum of the product standard cost divided by the actual sum of the product standard cost. So, we’ll format this as a percentage, and we’ll drag that in. And you can see, that in 2007, we sold 33, or at least we had the product standard cost of 33% of the totality of all of the years.

If we put in another measure, which would be the percent of the visual sum, then exactly the same, that’s the sum of the product standard cost divided by the visual sum, and you’ll notice we have a measure which is aggregation divided by measure.

You can have a measure which is a measure divided by a measure, you can include as many measures as you want, as long as it doesn’t go into a loop. So, we can’t have percent of visual sum lying on percent of actual sum, and at the same time percent of actual sum lying on percent of visual sum, for instance. So, let’s drag this in. As you can see, the percentage of the visual sum will always equal 100%. So, you can report on what the percentage is in this one particular year, and then you can report on what it is over the entirety of the time period. So, if I remove this filter. We get back to everything, but if we go back to back to the report. Just close one or two of these things down. There we go, there’s our report.

Obviously, I would want to do a lot of formatting to make sure it is of good size, but what we can do now is add in a slicer, for instance, and this slicer could be based on anything. For example, the order date year, and when I move the slicer you will see that this actual sum remains the same, but the visual sum changes. The percentage of the actual sum remains at 100%, but the percentage of the- sorry, the visual sum remains 100% but the percentage of the actual sum keeps changing.

So, ALLSELECTED, very easy to use if all you want to do is see what is the total of what I can actually see. All you do is you put a calculate around, and then say comma all selected open bracket close bracket. So, imagine its use with slicers, imagine its use with filters, or any other calculation where you’re taking a smaller bit of the table than the entirety.

220. Other Functions

Now, on screen you can see some of the other filter functions. And it can get complicated very quickly. So, I don’t propose in this particular course to go any further in terms of the filter functions

For example, have a look at ADDMISSINGITEMS. You can see that there are two different syntaxes and we can see how complicated it can become.

So, for me, the biggest, most important functions are ALL, ALLACCEPTED, ALLSELECTED. The CALCULATE function. The RELATED and RELATEDTABLE.

So, of less use, perhaps, are things that would return a Boolean at least at this stage. For instance, HASONEVALUE is one distinct value now that everything has been filtered down which has a TRUE or FALSE.

If you want to go down further, then my suggestion would be to have a look at EARLIER and EARLIEST. So, with all of these functions we’ve got the tag links and you can it does go into a fair bit of detail as to what each of these functions do.

Therefore, my suggestion is at the moment, stick with these seven different functions. So, ALLSELECTED allows you to have a visual total.

ALL takes the entirety of the table.

ALLEXCEPT removes certain filters.

FILTER allows you to go down further.

RELATED and RELATEDTABLE allow you to go up or down a chain.

But this is probably the one you need to be most familiar with, CALCULATE. Because a lot of these functions, if you get a table, you will need to do some sort of aggregation. Some sort of calculation. So, start with CALCULATE, then do your base expression which can probably stand alone as it is and allows you to do a certain amount of error checking. And then add your filter at the end. It will adjust the context accordingly for your base calculation.

• Category: Uncategorized