The most important concept in understanding DAX is context. There are three main types of context: the evaluation context, the filter context, and the row context. You may watch the full video of this tutorial at the bottom of this blog.
When DAX calculates something in Power BI, it works via a two-step process. At first, it evaluates the context it is currently in. Once it has done that, it then decides how am I going to calculate these results and that’s where the other two contexts come into play.
In this particular article, we are going to focus on evaluation context. I will go through a simple example here to explain what it actually means.
Understanding Evaluation Context Through Examples
What we have here is a simple table that is measuring the number or quantity of what we have sold per day.
Let’s quickly evaluate what this formula is doing. It is summing up the Quantity column in the Sales table.
When we go to the Sales table, we will see that the measure is counting up the Quantity column as 1 for all rows.
To get the results in the Total Quantity Sold column, the formula we use is exactly the same. Nothing changes for any of these results. The only thing that changes initially is the evaluation context.
For every single row, we have a different evaluation context. And because of that, we receive a different result for every single row.
What Is Filter Propagation?
Now, what is happening behind the scenes is that for every single row here, a filter is being placed on the data.
Let’s go back to the data table of our model. With the evaluation context, the Date column comes from the Dates table. So a filter is being placed on the Date column here.
When we place this filter, it flows down to this relationship with the Sales table, and automatically filters the table. This is what you call filter propagation.
Any filter that flows from one side down to the mini side is going to filter that table.
So in this particular example, the context starts from the Dates table and then automatically flows down to the Sales table. And that is why we get a different result for every single row.
Whenever you audit or evaluate what a formula is doing, you need to think about the evaluation context every single time.
This is because everything always starts with the evaluation context. This is important as you start developing more and more advanced DAX formulas.
Evaluation Context In Writing Advanced Formula
Another great thing about the evaluation context is that it is the true power behind the DAX formula.
It enables you to get so many results quickly by writing simple formulas.
The same thing can also be said for writing advanced formulas. You can use an advanced formula, put it against a different evaluation context, and get numerous different results very quickly.
So we just did a small change here and added Product Name into our first table. The DAX formula did not change at all but the evaluation context did. This is why we have different results here.
The same process also works in this example, where the evaluation context for these results starts from the Products table and go through the Product Name column.
This process will filter every single row. So whatever specific Product Name is used, it will filter all the way down through the relationship, and then filter the underlying Sales table as well.
This is just a brief overview of what evaluation context is. All you have to do in Power BI is to change the context and you’ll get a very different result.
It is seriously important to understand how this operates because in your mind, you have to think what is the current evaluation context of the formula.
That is going to result in an understanding of why you are getting a specific result in a specific context.
Some recommended links for you so you can learn more about the application of the three contexts inside of Power BI:
Enjoy working through this one.