If you do not understand that concept, you will always struggle with DAX. Just comment CHEAT SHEET below and I'll send you my DAX Filter Context CHEAT SHEET which explains the concept in simple, easy to understand terms..
I'm Avi Singh, Power BI, Microsoft MVP, and through my online program on learnpowerbi.com I have helped thousands of business users master Power BI and build beautiful reports in under 30 days. So let's get started with our DAX Top Five countdown and.
Kicking it off and #5 are sum and count rows. Now I'm going to cover more like function categories in the top five than specific functions. But you can see these two are pretty simple and they're a great way to get started..
But don't does not mean that you can dismiss them. They are still incredibly powerful. So here we have a simple measure sum sales amount. So you just sum up and you specify a column you want to add up..
And of course, the beauty in Power BI is always this interactivity. So right now these charts and graphs are showing these two simple measures. But even here you can see the the spike in June and you can.
Click that and see exactly what was going on. And you can see how Australia has a big chunk of our sales. And you can click on that and see exactly how Australia was doing. So really cool stuff and a great way to get started with DAX in.
Power BI at #4 is calculate and I call it the magic wand of Power BI because it is so incredibly powerful. So here we have our original sum of sales function. And here we have just sliced it by the product category. And you can see sum of sales by product category..
But sometimes we would want to go beyond that and change how a specific measure is being calculated. And that is where calculate comes in. So if you look at this measure here in our formula #4 here we have let's go back actually first to the sum of sales..
So this is where we started sum of sales amount, right? But then we if we go to a new measure, it is saying, yes, calculate sum of sales and then we can specify one or more filter options. And here we have specified product category equals bikes..
Posts Related:
- Visão Geral Menu RIBBON Planilha Login Excel VBA
- Custom Variables
- Make Data Iterator & Array Aggregator Combining and Separating Bundles
And you can see how the results match up over here, right?
So sales bikes now it is automatically giving us this result. So that is pretty cool. But of course, we can go keep going like that..Let me show you a few. One other option here. So now again, this is a display where we have used a matrix to slice and dice by product and product color. And let's say we were only interested in the red bikes..
Now we can see this value here, but it's quite possible that maybe there is somebody in charge, vice president somewhere who's only focusing on red bikes. And for that you can define a measure which specifically shows just that..
And how you going to do that? Well, that is simple. You're going to specify another filter option to calculate. So now we're saying calculate the sum of sales, but product category is bikes and product color is red..
Now calculate is capable of so much more than that. And you're actually going to see that with the friends of Calculate that we're going to cover in the rest of the countdown at #3 we have our X functions. And there is a mystery X function, which actually turns.
Out to be one of the best friends of Calculate. But let's stay with X functions. Now they have a pretty similar format. So some X and you specify a table and then an expression and X in X functions stands for iteration..
What X means is to go row by row in the specified table and it's easiest for me to show it with an example. So let's say our sales data was coming in and it did not have a column to show the the sales amount for each row. Instead, all we had was the unit price for the product that was.
Sold and how many units were sold. Now, of course, you can see that to calculate the total sales amount for this row, we have to go row by row, and we have to multiply the unit price with the order quantity to get that sum. And then we have to repeat it for every single row..
Whenever you are in a situation where this kind of phrasing is coming in your mind that, oh, I need to do this row by row, you're going to need an X function because those are the iterators. So in here, I'm going to show you the formula that we have.
Used, and it's super simple.
It's the sum X function. And if you go through here, you can see it's a really simple formula, sum X. So X means iterate..Iterate over what? Iterate over the sales table. OK, I'm going to go row by row, iterate over sales. And then for each row, what would you like me to do? And that is the last part..
So I'm saying for each row, calculate this and so it goes row by row through this table, calculates this and then it says all right, I did all that, what do you want me to do next? And this is the funny part that the sum actually happens at the very end..
Now of course, if you swap it out with the average X, min X, or Max X instead of summing it up, it's going to give you the average of the results, or the minimum or maximum. But you can see how X functions are pretty intuitive to understand once you know what's going on..
Once you have calculated this measure, you don't have to necessarily only display like this. It acts like any other measure. In fact, we could go back to the charts that we had built using our original sum measure, and if we didn't have that, we could.
Build it using our new sum X measure again. Once measures are built, they have the magic of define once, use everywhere. What that means is you can use it in in any chart and graph and you can slice and dice it using any of the columns in that table.
Or any of the related lookup tables. Now it's time to reveal the bonus mystery X functions, which is very good friends with calculate. So my friends, the mystery X function is none other than filter..
Now this is where a lot of Power BI beginners get confused because filter is not named as filter XI. Really wish it was. Now if you look at even the syntax of these functions, it is so similar..
So look at the syntax of some X and especially pay attention to this expression as a first parameter is obviously just the table you're iterating over. Now this expression says this expression is the expression to be evaluated for each row of the table..
Now you want to read the description for filter function. It doesn't say the word expression, but notice the text. It's very very similar, a boolean expression that is to be evaluated for each row of the table. So my friends, filter is also an X function just like all of.
These other ones. But what makes it unique is that it returns a filtered set of rows instead of doing a calculation or math like some average min or Max. And that is why filter is often used along with calculate at #2.
We have the all function. And yes, this is also a friend of calculate and is often used with that. Now all function is deceptively simple, but people often get it wrong..
But you're going to know what to watch out for. The first thing to understand is that all comes in two different flavors and I call them hammer and chisel. The first format is where you pass a table name to all, and this one is what I call the hammer because it removes all.
Filters on that table. The other one is chisel and it gives you more control where you can specify either one column or multiple columns, but you can see how this would give you more control. Let's see this in a simple example..
So let's say we have our sales by month and we want to just calculate what percentage of sales each month is when compared to the full year. So for this we can write a simple measure which takes the sales measure and then uses the all function to clear the filter.
On the month column. Now let's see the syntax for this. So you're going to say calculate the sum of sales and then you're going to clear the filter from the month function. And that is what all is doing now..
The way I've named this function may have given you a clue that it actually is supposed to work. Should have worked, but it doesn't work. And I remember the first time I encountered this I was pulling my hair out..
But hey, you're going to know exactly what what to do because I'm going to tell you in a minute. All right, so this was frustrating because I am using the all function, but it doesn't seem to be doing anything. Now, this is one gotcha in Power BI because the filter that I'm.
Trying to clear on this column has a sort by column property set. What does that mean? If you were to go over to the calendar table and I find the month column which is being used here, if you check the sort by.
Column over here, then you're going to see that it is sorted by the month number, which makes sense. It'll be silly to sort months alphabetically. So we do want to sort it by month NUM, so that January, February, March and so forth..
Of course you can change that if you have a fiscal calendar starting in July or some other month. But the the trick is or the gotcha is that when you have to clear a filter on a column which is sorted by another column, you have to include that column as well..
So really in this case, the way we would write our measure is to say that Yup, all calendar month, and then we have to specify another column, the calendar month NUM. Now that you know it, you're never going to get this wrong. And once we have that, we can define a simple measure which.
Just takes the sum of sales, which is this one and divides it by the sum of sales for all months, which is the the full year. And that way we get the percentage. And I've shown that measure here and we have added some condition.
Formatting to kind of make it look awesome. So far we had looked at the chisel version of the all function. Let's look at the hammer. So the hammer if we look at this uses the all table format..
So in this case we are saying all and then we are instead of specifying column or a list of columns, we're saying a table name. And what that does is it brings down that hammer and clears every single filter on that table..
And you can see how our results are different. Whereas this one was only clearing the filter on months and that way we were getting the year in total, What this one is doing is it's clearing all the filters on the calendar table and we're getting a different total..
Now, of course, you might be wondering where are the other filters coming from? In this case, they are coming from the filter pane where we have a specific year filter. And the Hammer version clears this filter as well, which is.
Not what we were looking for in our case. But now you know about these two versions, and you're going to know when to use which one. And sitting at number one are the DAX time intelligence functions..
And these are the top because they help you solve a lot of challenges that were really, really tricky in the old world, especially for me as I was trying to do these things in Excel. Let's walk through some examples, starting with the year.
