DAX - FILTER inside CALCULATE

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

Let’s look at this by way of example

=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

The Ultimate Excel Formulas Course

• includes XLOOKUP and will soon include Dynamic Arrays

Become a Power Pivot Hero


GET and TRANSFORM DATA like a PRO
Power Query Excel 365

Learn DAX for Power Pivot and Power BI

In return for this DAX Tip – FILTER inside of CALCULATE – I ask that you share this post with your friends and colleagues

This blog is Powered with STEEM and The Excel Club is home of the Excel Learn and Earn Activity. Leave a valuable comment below that adds value to this topic and you could earn your self some rewards.

---

Cross posted from my blog with SteemPress : http://theexcelclub.com/dax-filter-inside-of-calculate/

---