You know that DAX in Power BI is a powerful engine and must be used in your report to have exciting insights beyond the standards of information found in your columns.
And as powerful as it is, this engine is based on elementary concepts that are all about the context because what DAX does, is it performs dynamic analysis based on the current row, the filters activated, and other elements.
All these elements are what we call the evaluation context, and the evaluation context regroups these four topics:
- Row context
- Filter context
- Context transition
- Expanded tables
Most of the articles focus mainly on the row context and the filter context, but the context transition has some side effects.
If you want to feel comfortable using DAX, there is no way around it, you need to understand all of these topics, and we’re here to be your guide!
So what is the filter context?
The evaluation context is the environment in which your DAX calculations are run.
Suppose you run a company that sells electronics, and you want to know how many you sold? This operation is effortless. For us, we will execute this formula:
And as you can see below, the moment we add it to our report, it will give us the quantity of all my products which is the sum of all the rows of the table that has that column.
But as soon as you introduce some other elements in your elements, say you add a column and put it in your table, the result changes.
Why did the measure you wrote first calculate the sum of all rows while calculating some of those rows, not all together? The technique that is produced in the filter context.
The table creates a different filter context for each cell, so the engine computes the same formula under different rules, producing different results.
After completing the calculation, the filter context is destroyed and not used by any other process.
A common mistake made by beginners is to confuse the filter applied in a table visualization for a row context, but the row context is applied on physical tables and when using iterators, which is not the case here. If you think about it, the filters here are grouping multiple rows of the products of the same category, and it is not applied to a single row.
“Filter context applies on top of other contexts, such as row context or query context,” because you control the values used in the formula.
But the filter context is not only in a visualization table; every visualization in Power BI can potentially affect another visualization.
You can apply the filter context by clicking on a row in a table visualization, applying a slicer, using filters on the filter pan, or inside a measure.
Here I clicked on the bar of Los Angeles, so there is a filter that says “county can only be Los Angeles,” and if we look at the category slicer, we have “category can only be Keyboard,” and for the years “year = 2019”. If we see the filter as a table, we would have something like this.
The table might have many rows and columns, we will call the rows tuples, and each filter is a table.
Now, if we see the filter context as a set of filters, we will have:
- One filter is a table
- A filter has many tuples
- The entire group of filters is a group of tables forming the filter context.
And this is the basic idea of filter context.
ROW context
You can access all your table columns when you write an expression in a calculated column, thanks to the row context.
The row context exists only in particular conditions and allows us to write a column reference that means “give me the value in this column based on the current row,” so you’re not working on many rows simultaneously; instead, each row is treated separately.
We want to know the Total Sales for each row; we can either do it using a calculated column.
DAX multiplied the value of the price and the quantity row by row and then stored the result in the same row in the Sales[Total Sales] column.
When creating a calculated column, DAX implicitly knows that you want to perform a calculation row by row, and since it knows which row to work on in a table, we say that there is a row context open on that table.
If you created a measure that calculates for each row the same Total Sales value that we have in our computed column, you would need an iterator.
An iterator is something that iterates. It goes through a table row by row and calculates an expression during the iteration.
If you have previous knowledge about algorithms when you iterate inside a loop, you can access a specific element at a time, so if our element here is a row, you can access only the columns of your table in that row.
For instance, only the columns of Sales can be accessed here.
If we have a relationship between two tables, such as Sales and Products, DAX allows us to connect these two using “RELATED” / “RELATEDTABLE” inside your iterator.
Outside of SUMX, there is no row context; it is a measure. SUMX iterates over Sales, creating a row context over Sales. The row context is scanning Sales; therefore, you can reference any column in Sales, and inside SUMX, in its second argument, a row context is created by SUMX while it scans Sales.
Another mistake beginners make to confuse row context for a filter is the row context iterates row by row. It doesn’t apply a filter. The only filter that exists in the evaluation context is the row context.
To sum up, the row context can be found in physical tables – your tables are present in the model and created in iterators.
Are you looking for help with writing complex DAX projects?
Our experts at DASH can help you. Contact us today!
Besides, in your typical report, your result is based not on the row context only nor the filter context only. Everything works together to get your output, so how does it work?
At first, the filter context is applied. Sales is the table affected by the filter example in our previous example. The filter context affects aggregations and table expressions.
The rows retrieved from Sales are the ones that match the filters.
Then the row context is applied. In our case, SUMX is applied to the rows retrieved from the filter.
CALCULATE
CALCULATE is the function that you need to understand because it is the only function that changes the filter context before executing any calculation. If you want to be performant in DAX, you need to manipulate the context before running the calculation.
It is the most efficient way to get the data you have in your model. If you do otherwise, you might have a prolonged analysis because you’ll apply complex long measures on many rows. Sometimes millions, and it might be unnecessary. So we reduce the number of rows before executing anything!
For example, we would first get only the rows where the Category is Mobile, then iterate through those rows and compute the sum.
The first argument (SUMX here) is usually the last one to be considered, and we also need to consider the other filters present on the slicers.
At this point, we have two filters:
- The filter inside calculates what we will call the inner filter context.
- We will contact the outer filter context the filter present in slicers, filters pan…
Remember that a filter is a table, so when you wrote Categories[Category] = “Mobile,” what DAX does is generates a table with a single column (the category) with the list of values that we want. Here, we only wish to the Mobile category, and then any data that matches my table will pass through it.
Since we applied a filter inside our calculations, even if I have other categories in my table, it will only show the total mobile sales no matter the category. See it this way, what I do in that formula is:
- Remove any filter applied on the category column (using All)
- Then apply my filter of category = “Mobile.”
In case I don’t want to rewrite the filters, I can use KEEPFILTERS, which will keep both filters:
- At first, I have a filter on categories: Earphones, keyboard, mobile…
- Then I apply my second filter, and since I have just one value that matches, the other ones are blank.*
Expanded tables
Any model you’re going to work with will have multiple relationships. Those relationships will link tables together, but they also mean that if a filter is applied to a particular table.
If that table has a relationship with another, the latter will also be affected by that filter. We say the filter is automatically propagated to the other table through a relationship.
This filter propagation happens only in the direction of the arrow drawn in those relations.
For example, a filter might go from Products to Sales.
As a best practice, we only have one too many relationships. Whenever you create a one-to-many relationship between two tables, you expand one table using the columns from another table (just like a left join).
That’s why I can filter my sales by category, product… Because it is like applying a filter on one big table.
Context transition
We saw in expanded tables that relationships affect the filter context and propagate through one side of the one-to-many relationships, but row contexts never propagate through relationships.
For this example, I will use a calculated column since it has the row context, and I will calculate the quantity sold for each customer:
See how we have the sum of all quantity purchased in each row, and if we want to have it for each customer separately, we can only make it work if we use context transition.
To summarize, context transition happens when we used to calculate, transforming the current row context into a filter context so that the relationship would work.
The only exception is using measures because measures naturally have to calculate wrapped around them, so they have, by nature, context transition. Here I have a measure named Quantity Sold that I applied in my column:
And I got the result I wanted without using CALCULATE:
In this article, we saw four key concepts that influence your output in DAX and how the filter context and row context work together, and how we can deal with more complex situations.
This is just the beginning for you to understand DAX.
There will be more articles about these concepts. We hope that by now, you have a better understanding. If you have any questions, feel free to ask, and your feedback is always appreciated.
I love it, clear information.