CALCULATE in DAX is such a powerful and complex function to fully understand. In essence what CALCULATE will do is modify the current filter context. And it does this by evaluating both the current filter context and the filters applied by CALCULATE.
The syntax for CALCULATE is:
=CALCULATE ([Expression], filter1, filter2…..)
So if CALCULATE contains filters, why and when is the FILTER Function used within CALCULATE?
This question has been put to me a number of time. In fact, when I was first learning DAX, it was a question that I had my self. So in this short article, I will do my best to explain why and when you would use the FILTER expression explicitly inside of a CALCULATE expression.
Limitations of CALCULATE in DAX
Powerful and all that CALCUATE is, there is a limitation to the filters that it can apply. The filters will only work when
- There is a value on the right hand side of the equation
- There is a column name on the left hand side of the equation
=CALCULATE (
[Total Sales],
[Total Sales] >= 700
)
If we were to enter this expression, our logic would be correct. . We are looking to calculate the total of the sales where the total sales is greater or equal to 700. But the problem with this expression is that we have a measure [Total Sales] on the left hand side of the equation. We are expecting a column name
Solution – Just add FILTER inside CALCULATE
If we now re-write the expression to:
=CALCULATE (
[Total Sales],
Filter(Sales, [total sales] >= 700)
)
First thing CALCULATE will do is evaluate the FILTER expression.
Filter is an iterator and iterates the Sales table. But the filter might not be able to see all of the Sales table. This is because of any original filter context applied by the pivot table or visualisation. So the reference to the table for the filter argument is the portion of the table that is visible under the original filter context.
Let’s say we had a pivot table showing only 2017, First Sales would be filtered to 2017 as per the original filter context. Then FILTER will go over 2017 and check to see row by row if each sale is greater or equal to 700. A table will be returned with the rows that match this criteria.
Next CALCUALTE will step in to remove or amend any further filters on this newly generate table and then calculate the total sales.
This is just a very quick example of advanced filter context when working in DAX. Keep an eye out for further articles that will explore this more.
Sign up for my newsletter – Don’t worry, I wont spam. Just useful Excel and Power BI tips and tricks to your inbox
SIGN UP NOW
- includes XLOOKUP and will soon include Dynamic Arrays
Power Query Excel 365
In return for this DAX Tip – FILTER inside of CALCULATE – I ask that you share this post with your friends and colleagues
Cross posted from my blog with SteemPress : http://theexcelclub.com/dax-filter-inside-of-calculate/