Filter context is one of the major topics that any Power BI user should initially learn about, especially if you want your DAX calculations to work effectively. You may watch the full video of this tutorial at the bottom of this blog.
In this article, I will run through the filter context. Every DAX formula is calculated in a two-step process.
DAX Formula: A Two-Step Process
The first step is the evaluation context or the initial context. Then DAX makes a decision based on the function that you write or the type of calculation that it is going to do.
It branches out into the second step, which is either the filter context or row context.
There is a very big difference between these two contexts, even though you can actually get the same results by writing different formula.
To truly understand how DAX works, you need to understand the difference between the two and how things are actually calculated in memory through DAX.
Understanding The Filter Context
The filter context is relatively straightforward. It is relatively simple because it really aligns to the simple aggregations that you do. To understand what is actually happening, let’s run through a very specific example.
We have two examples: total quantities sold per day and total quantities sold per product. These two have exactly the same formula, but with different evaluation context.
Let’s walk through exactly how this is calculated for the very top row in each of these tables.
As you can see on the top row, we have sold 24 products on the first of January 2015. A filter is placed on the Date for the first of January 2015 for the calculation to work.
If we jump to data model, we can see that the filter placed on the Date flows down through the relationship until it goes through the Sales table.
In the Sales table, the purchase date is filtered for the first of January 2015. And when that filter is in place, all that remains will be the first of January 2015.
Aggregation In The Filter Context
Since we’re summing up the Quantity column in this function, the aggregation in the filter context does this one big evaluation or big aggregation of the entire column.
The filter context simply means that the filters are put in place. This will make DAX look at the entire column that’s left over and then do an aggregation of everything all at once.
The same can be said for a variety of different calculations. You can do this while you are trying to count up the total transactions that you were doing or the total revenue that you are creating. The same aggregation can be done after the evaluation context is completed.
There are so many context in DAX that it can get confusing at times. One tip that I can share with you is to associate filter context with aggregation, like a sum or an average, or a min and max. This is the opposite of an iteration, which is what works with row context.
***** Related Links *****
How the DAX Calculation Engine Works
ALL Function in Power BI – How To Use It With DAX
What Is Context Transition And Why Does It Matter?
Conclusion
To sum up, a filter context works after the initial evaluation context is done. The key is to associate it with an aggregation. What happens behind the scenes is that it does this one big aggregation of a column after all the filters have been put in place.
You can look into these links to learn more about the application of filter context inside of Power BI:
Evaluation Context – (1.8) Ultimate Beginners Guide to DAX
Explaining Row Context – (1.10) Ultimate Beginners Guide to DAX
Enjoy working through this one.
Sam
