As you start getting more comfortable with DAX, you’ll quickly discover that there are multiple ways to skin a cat. In this quick tip, I’m going to try and explain how calculate with filtering works, and more generally, how to use calculate when possible.
Let’s jump straight in with an example.
What if you wanted to, for example, sum the amount of sales where the amount was greater than one?
We could do something like this:
Measure = SUMX ( FILTER ( Sales, Sales[Quantity] > 1 ), Sales[Quantity] * Sales[Price] )
Of course this would give us the correct answer, but it isn’t the most efficient way of working out what we want.
Watch your filters
Before we move onto the more efficient method using the CALCULATE function, I want to point out why the above formula isn’t as efficient as it could be. It all comes down to the filter.
In the example, I added a filter to the Sales table in order to only return rows where the Quantity is greater than one. When looking at the Sales table, this filter function will literally look through the whole table. Not just every row, but every column in the table. Even though we’re only concerned with the Quantity column. As such, it is quite “expensive” as a calculation.
Calculate with filtering
To be fair, we could fix the filter issue by doing something like this:
Measure = SUMX ( FILTER ( ALL ( Sales[Quantity] ), Sales[Quantity] > 1 ), Sales[Quantity] * Sales[Price] )
By adding the ALL function and specifying a single column, it ensures that the filter just focuses on that column. Except now the above calculation wouldn’t work. Due to the fact that we are now filtering the table down to a single column we can’t multiple quantity and price, because only quantity is available.
We can fix that by creating a really simple measure that just multiplies Quantity and Price and does nothing else to give us Sales Amount. However, now that we have that the Sales Amount measure, we can then just do the following using the CALCULATE function:
Measure = CALCULATE ( [Sales Amount], Sales[Quantity] > 1 )
Woah… Much simpler right?
This calculation is just filtering the Sales[Quantity] column for me without me having to explicitly tell it to.
In addition, by using the calculate function it is just much more efficient. To put it into perspective, on a relatively small dataset that the first calculation shouldn’t have too much trouble with, it was four times slower than the above CALCULATE query.
So there you have it. If you weren’t already doing it, use CALCULATE with filtering.