Power BI works by evaluating DAX expressions over tables and columns in our model. Our understanding of how Evaluation Context and DAX interact ensures we bring dynamic and accurate results to our report users.
Before we dig in, let’s define some key terms and how they relate to our model and report:
When I use the term “Report”, “Reporting Canvas”, or “Report Visual”, I am referring to the blank canvas in Power BI. The report canvas is where we design the report that our viewers will view.
When I use the term “Model” or “Tables”, I am referring to the data tables in the background that support our report. Related tables work through model relationships and provide the foundation and flow of our analysis.
In this post we are going to cover the two evaluation contexts (filter and row context), as well as the two types of DAX calculations (measures and calculated columns).
What is Evaluation Context?
The textbook definition of Evaluation Context from our Italian friends at SQLBI reads like this:
“An evaluation context is the context under which a DAX expression is evaluated.” – Definitive Guide to DAX
It is Evaluation Context that determines, calls for, and applies filters over table columns, or moves row-by-row within a table in structured way. Our choices of context include Filter Context, Row Context, or both! Evaluation context really does the hard work of gathering the appropriate raw data for each one of our expressions.
Our responsibility is to be aware of what raw data is being used when the expressions we write are evaluated. This can be challenging to learn new functions, and also properly envision how Power BI is applying and using different subsets of data in the background.
Below is a very surface illustration of the differences between Row and Filter Context. In the below image I use the heading “Reports Filter” to illustrate that Filter Context over columns combine with our expression on our report visuals, and the heading “Tables Iterate” to illustrate that the default behavior in a model (or data) table is to evaluate row-by-row.
Evaluation context seems logical on the surface, but comes with a steep learning curve. Take it slow, practice using good and bad examples from trusted sources such as SQLBI. Once you are comfortable with the fundamentals, you will be able to share insights with confidence.
If we are in a table row, we isolate a single row; there are no filters by default. Our expression evaluates by iterating row by row (Row Context) much like we would expect from a calculated column in Excel.
If we are looking at a specific point (or cell) in a report visual, calling a measure reference, or using the CALCULATE function, we know that our model has filters applied over the model to meet the report requirements for each specific cell; only then does our expression returns a column aggregation over the surviving table rows in the model (Filter Context).
Both contexts work together to bring correct result back to our report visuals.
What is DAX?
Once we understand how Evaluation Context behaves, we can start writing DAX, or Data Analysis eXpressions. DAX is a functional query language designed by Microsoft. DAX gives us the ability to write single dynamic expressions that utilize evaluation context to produce different results based on the requirements of each cell or datapoint on our visuals.
See the below illustration of how Evaluation Context and DAX work together.
What is Filter Context?
Filter Context ultimately evaluates each DAX expression based on the location of each cell in the report. As mentioned previously, tables are filtered back in the model to create a filtered subset that match the filter requirements of the report cell and our DAX expression.
A measure is a DAX expression that we write as an extension of analysis in our model. We don’t see the results of a measure until it is used on a report visual where it uses filter context in its final evaluation on our report visual.
Not having to materialize the result of a measure in our model helps reduce the weight of unnecessary physical table columns, while also increasing the flexibility of our calculations.
In order to see the various results from a measure, we drag it into our report visual on the canvas, as each cell result depends on its physical location on the report visual.
See below how dragging the Measure (Total sales) into the report visual automatically divides out our total of $565 by each Name appropriately. This is only possible in our report through the automatic Filter Context behavior that works to filter a unique view of the model for each cell/graph element in our report visual.
Our DAX expression evaluates over a filtered subset of our model to return an aggregation that is specific to only that report cell.
Seeing is Believing!
Click on the linked report that allows you to explore and visualize an example of a filtered model cell by cell in the report visual.
It might be difficult at first to imagine that every time a report user clicks a selection, they are filtering the model possibly thousands of times to generate different cell results on the report canvas, but that is exactly what is happening, thanks to the speed and compression in Power BI.
What is Row Context?
Row context is the default context when we add a calculated column to our table in the data model. Row Context serves a completely different purpose from filter context but is equally valuable.
Instead of filtering tables back in our data model and calculating math over our column of surviving rows, Row Context isolates each row one at a time to produce the correct result. In Power BI terms, we say that Row Context iterates (this means it repeats our expression one row at a time) over table rows.
Row context is used when we write a basic calculated column (such as Qty x Price) in a physical table, or when we do this virtually in a measure using the SUMX function. It’s automatic behavior ensures we first calculate our expression one row at a time. When we use this column or SUMX measure result in our report, the entirety of each row result is finally aggregated on the report visual.
“Row Context evaluates our expression by iterating (repeating) over a table row-by-row.”
This brings us to the second expression type in DAX, the calculated columns. Much like adding a new column in Excel, we can add a total sales column to multiply our quantity column by our unit price column.
Calculated columns are tempting to use as they are easy to create and visible to the report developer; however it is not best practice to use them especially in your fact table. Adding additional physical columns to your model, especially if it adds millions of new cells, can add unnecessary weight and slow down performance.
In this post we’ve touched on the basic concepts of Evaluation Context, which include both Filter and Row contexts.
Filter Context can’t do what Row Context can do and vice versa. There are times you will use one over the other, and times when you’ll use both in the same formula.
Learning DAX requires first understanding how each context interacts with our model. Both contexts play important roles in enriching our analysis.
Improving your ability to write DAX effectively takes continuous practice and carefully validating results. I enjoy learning from others that know more than myself including Marco Russo and Alberto Ferrari from SQLBI. For someone new to DAX, I recommend starting with Matt Allington’s Supercharge Power BI. I also provide links to other learning resources to help you learn these foundational concepts.
Thanks for reading! – Mark