DA-100 Microsoft Power BI – Part 3 Level 6 – Filter and Value Functions Part 2

5. FILTER

Now, suppose I didn’t want the entirety of the table, which all does all changes the context as a 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 duct standard cost and that there is a field called promotion key.

And you can see that the vast majority of the sales have been on promotion key, 115 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 key is equal to one. And I don’t want it 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 dim product category. Obviously, if I put it into fact internet sales, then I could 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 SUMX. So we’re going into the fact internet sales 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 we 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 SUMIF. You can put these equals or not equals to within more advanced SUMIFS. So here I’m saying give me this table, but only where the promotion key is equal to one. And then with that reduced table sum x the product standard cost. So now you can see the total has gone down for banks from 16 million to 15. So this is the photo function. It returns a table that represents a photo down a subset of another table or expression.

6. CALCULATE

Now another way of using all and photo is using the calculate function. So currently what we’ve got is the sum of this table once it’s being filtered of the product standard cost and similarly with the percentage of total, what we’ve got is the sum x of all of this table. But we could just specify the table and then put around it a calculate. So that’s our basic expression. So this could 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 it by saying I want all of fact internet sales and you will see that it gives exactly the same figures as before. Similarly for this product standard cost promotion key one, we have modified the table here in this table part. But if instead we get rid of this and we just have some x of fact internet sales and then around it we put a calculate and say we are going to filter that. So what is that? We’re going to filter the fact internet sales, we’re going to filter that when the promotions key is equal to one. 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 as 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 said by the way, we want it on all of the fact internet sales table. Similarly when that was filtering out.

So that was expanding the context. When we’re filtering in, reducing the context further using this SUMX without the calculate, we have to say our table is filtered down right at the beginning using calculate we can 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 why the promotion key is equal to one. 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’d be able to say okay, this is my current context and I want it to be expanded or filtered however I want very easily.

7. ALLEXCEPT

Now previously we’ve had to look at the all function, all removes, all of the context, all of the filters and we also had to 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 roles 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 or dollar 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 older 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 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 standards, 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 pound figure for the accessories. In fact I’m going to change it back to a matrix because I like the fact that I can see totals. So there’s our 323 and this gives us the all because that’s what we’ve got here. But in that removes all of the context, it removes category, it removes order 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 all the 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,000 figure. So what I need to change this to well, firstly, all accept 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 calculated the beginning and then all of internet fact sales at the end. So now we can change this. So we have all except so this bit in red that we 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 color. So in the Power Bi model I think that’s the English category name or something like that. So that is the basics of our new measure. So let’s go into Power Bi and change this measure to 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 could 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 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 photo. 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 voters except for certain photos and it seems to work best if you put a calculator around it and have all except at the end.

8. ALLSELECTED

Going back to the all functions. ALLSELECTED is a really good function for getting a visual total. So you can see what it says, removes context filters from columns and rows in the current query, but retaining 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 I’ve 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 $877,000. Now, the entirety of the table is well, it’s $80,450,000. 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 are is nothing in the brackets. So if I add up column C, you can see it is $877,077 or $7 here. Our visual total is 877007. 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 is $38,000. 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 remove the accessories bit. So it gives us the visual total for 2002. So if I submit that, you can see 91,000. So it gives us that in bikes, in accessories, in 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’re 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 calculate of the sum of the product standard cost. Close the 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 $17,277,000 in each and every line. So now let’s add in a measure based on the all. So we have new measure and this is going to be the actual sum of a product standard cost. And again, this is equal to the calculator 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 let’s 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’ll 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 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 cast 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 notice we have a measure which is aggregation divided by measure. You can have a measure which is a measure divided by 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 relying on percent of actual sum, and at the same time percent of actual sum relying on percent of visual sum, for instance. So let’s drag this in and you can see that 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 now if you go 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 a 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, at 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 visual sum remains 100%, but the percentage of the actual sum keeps changing. So all selected 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 calculator around and then say comma all selected, open bracket, closed bracket. So imagine it’s used with slices, imagine it’s used with filters or any of a calculation where you’re taking.

9. 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 add missing items. You can see that there are two different syntaxes and you can see how complicated it can become. So for me, e. The biggest, most important functions are all, all accept and all selected. The calculate function, the related and related table. So of less use perhaps are things that we turn a boolean, at least at this stage.

For instance, has one value, is there one distinct value now that everything has been filtered down? So returns are 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 got these hyperlinks and you can see 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 all selected allows you to have a visual total. All takes the entirety of the table, all except removes certain filters.

Filter allows you to go down further. Related and related table 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 end it will adjust the context accordingly for your base calculation.