Power BI evaluates DAX expressions directly over tables in the model, not over visuals we see on the reporting canvas. Understanding evaluation context and how it works is the core foundation of advancing in DAX.
In this post we are going to discuss the basics of both filter and row context, then walk through DAX examples that both filter and evaluate results back to our report users.
Please download the below Excel file that containing a copy of the small sample model tables used in this post.
Key Terms
When we use the terms “Canvas”, “Report“, or “Report Visual“, we are referring to the Report View in Power BI. The report view is where we add and design visuals that our report viewers will ultimately interact with.
When we use the term “Model” or “Tables“, we are referring to the schema of related raw data tables that form the model and support our report.
When we discuss row context, we use the term iterate and row-by-row evaluation. They both refer to automatic behavior in the row context model environment. Iterate basically means isolating a single row and moving from one row to another row, where evaluation is the math or logic applied over each row (Unit price x Quantity) during each iteration.
Power BI works best in a star schema consisting of one fact table of transaction data to be aggregated in our report. Surrounding dimension tables help us describe the context of our transactions in our analysis.
The star schema model is critical for providing a high degree of flexibility and performance for both the report developer and our end users.
Evaluation Context
Evaluation Context describes two widely used environments within Power BI called Filter and Row Context. Both serve different purposes and are often used together to evaluate results.
As we discuss evaluation context, we should ask ourselves three questions:
- Is automatic filter or row context present?
- Do I need to explicitly add filter or row context in my DAX?
- Are other modifications needed in my DAX?
If you remember one thing in this post, remember this:
⭐️ Filter Context automatically ‘eliminates model rows’ via measures on the report canvas. Row Context automatically ‘evaluates each model row’. In this post stop and read every term related to eliminating and evaluating, and think filter and row context respectively.
Thankfully we control this default behavior, but before we explicitly go against the grain and start eliminating rows from within a model itself, or evaluate each row prior to a report aggregation, we first need to understand the default behavior.
Filter Context
Filter Context works through measures. When a measure is placed on the face of the canvas, it automatically catches visual labels and slicer selections (in the current filter context) from our report to generate a table (sub) query behind the scenes. I envision a big fishing net that can’t help but catch all the surrounding visible label values applied as filters for that one point or cell in the visual. Instead of the term filter context, we could use ‘filter catcher’!
The purpose of this temporary sub query is used to filter the model columns, eliminating rows that don’t meet the current criteria of our visual.
This sub query table first filters our dimension tables followed by a chain reaction of related filters down to our fact table. This process is called filter propagation, and it happens automatically over the entire model.
When we look at the different visuals on our report we should first determine if a ‘current’ filter context exists or not. More often than not our report contains many dimension labels and slicer selections and combinations defining the need for many queries to satisfy each current filter context cell by cell on the visual.
Let’s consider the different cells below that represent a simple report visual and ask ourselves:
Is filter or row context present?
The mere fact that our report visual above includes an intersecting value cell between color Blue and year 2022, indicates that we do have current filters active in this cell. When asked if filter context exists, the answer is 100% yes.
The next question is naturally “What is the current filter context”? Or, what report labels are used to eliminate (filter out) rows in the model that don’t qualify for this cell result? The answer is Color = Blue, and Year = 2022. This observation defines the filter context for this cell ($3,400). The measure applies the row and column labels from this report to generate an automatic query that passes through the model as a filter.
Let’s expand the view of our report (below) and show that we now have the month of January selected in a slicer. Although this month selection was made by our report users, this too plays a part in defining filter context.
This additional filter further defines our current filter context and our sales dollar result narrows from $3,400 to now $1,500. When asked what the current filter context is for this cell, the answer is color = blue, year = 2022, and month = January.
But wait there’s more! Our report actually contains many different current filter contexts… Green 2022, Green 2023, Yellow 2022, Yellow 2023, and so on.
To round out this example, we acknowledge that there is one cell in this visual that seems to require no filters whatsoever. The grand total of $13,800 does not define any filters. In this cell we need the entire ‘un-filtered’ table to correctly return the grand total of sales.
To say this a different way, we have 11 cells that each contain a different and unique filter context (each of the year columns and color rows), and one cell that requires no filters at all, our grand total of $13,800.
On our canvas, filter context is automatically applied prior to the aggregation except in the grand total.
Measures
If we were asked by our boss to manually solve for the sales value based on the filter context of color = blue, year = 2002, and month = January, we’d head back to our sales table and filter the table down manually. Then we’d sum up the total sales column only containing two surviving rows. In this case our two rows would total $1,500 ($500+$1,000).
When write a SUM measure and drag it into the values box below, something magic happens; filter context followed by our SUM expression kick in and automatically work together to return the correct result regardless of it’s position in our report visual!
Beyond catching fillers on the canvas automatically, How does Power BI accomplish this? What specifically triggers the collection of these filters into this magic sub query?
The magic function used (that we don’t see in this measure) is CALCULATE. CALCULATE is the most powerful function in DAX, that always activates our filter catching net to eliminate non-relevant rows in our model AND evaluate results!
We can’t have filter context without the presence of CALCULATE (seen or unseen) catching the filters, and we also can’t have a filter context result without aggregating our result. CALCULATE IS filter context!
⭐️CALCULATE is the only function that eliminates and evaluates model rows!
SUM with an unseen measure CALCULATE is just one of many single column aggregations used in DAX. Single column aggregations are simple and efficient, however they highly depend on filter context to work properly. Other similar functions include: MIN(), MAX(), AVERAGE(), and COUNT().
Click here to explore DAX functions further in DAX.GUIDE
Before moving on to row context, let’s restate the basic steps of what we’ve just worked through with filter context.
Step1: Filter context is driven from measures on our report visuals through a hidden CALCULATE surrounding our measure. Color = Blue, Year = 2022, Month = January
Step 2: Filters defined in our report are caught in our CALCULATE net and used as filters over the respective dimension tables in our model.
Color = Blue filters our Part dimension table in our data model (illustrated below). Year = 2022 and Month = January both filter our Calendar dimension table. Both Part and Calendar dimension tables are filtered accordingly and do their part in step 3.
Step 3: The filters continue from our dimension tables through to our related Sales fact table. This is called filter propagation and eliminates non-relevant rows from our model result.
Step 4: After all table filters are in place, our SUM measure expression finally evaluates (technically row context) over each surviving row left in the sales column. It’s this single filtered sum result that returns back to the specific cell in our report (Blue and Jan 2022).
This four-step process happens as many times as necessary to satisfy every filtered point or cell on our report canvas.
If you’d like to see this in action, click on the sample report below and hoover over each cell in the report visual. A pop-up by cell illustrates how our model filters in the background.
Row Context
Filter and row context have different purposes and as we just saw above, work hand-in-hand to provide results back to our visuals.
We just learned the simplicity of a measure aggregation above, however there are many times we need to first evaluate each row prior to the final aggregation.
⭐️ Remember, Filter Context aka CALCULATE automatically ‘eliminates model rows’ through measures on the report canvas.
Row Context automatically ‘evaluates each model row’.
Row context is needed when simply eliminating model rows through measure filters followed by a SUM expression would return incorrect results to our visual. In cases like these we must intervene and be explicit about adding row context.
If we first need to multiply two columns together such as Quantity x Unit Price, we need to explicitly evaluate each row before the SUM expression. Functions that help us evaluate row-by-row are called Iterators.
Let’s now focus on using DAX that evaluates each row between two columns first before aggregating (SUM) our final results back to our report visuals.
Calculate Total Cost
How do we sum total cost when our model table does not provide this in a single column? We do have two columns (Quantity and Unit Cost) that we could use to create total cost, but it will require multiplication first.
Let’s try a couple of wrong attempts to appreciate the need for row-by-row math. Let’s multiply Unit Cost x Qty columns in a measure.
This does not work as Power BI is not detecting this column Unit Cost as we type out our measure. The reason is that every measure we write must evaluate to a single result (knows as a scalar value), thus an aggregation function (SUM, MIN, MAX, COUNT etc.) is required.
Let’s try SUM in our measure and place this measure in our report visual below to see what happens. Here we have two SUM aggregations multiplied together.
We’ve satisfied the need to use aggregations in our measure; however, something looks wrong. It does not make sense that our cost far exceeds our sales on blue products. Also, if we add up the Total Cost (Wrong2) column, our total of $189,380 does not add up!
If we add our SUM(Unit Cost) and SUM(Qty) as separate columns in our report visual our issues become clear.
This measure is working backwards! The SUM function first sums our Unit Cost column and our Qty column independently.
Next the two sum totals are multiplied together! We now realize that the grouping on our report visual is not playing well with our formula; everything is aggregated then multiplied together. What we need is to perform this in reverse… we need to multiply Unit Cost x Qty in each row first, then sum the resulting third result column to land on correct totals.
We could use the Excel approach and calculate this using a DAX calculated column in our sales table. This time, rather than calculating a Measure, I am going to use DAX to calculate a “Calculated Column”.
Now we’re talking! SUM back to our report visual is not present just yet, but we should be happy in we found a way to combine two columns into a single total cost column before summing our result on our report visual.
All that’s left to do is write a SUM measure over our calculated column.
Calculated columns are great… in Excel 😔
Calculated columns allow us to see with our own eyes how this math works row-by-row. It’s only natural that a measure over our this added column would work well in our report visual.
Even though our ‘Excel approach’ works, it is not best practice in Power BI. We somehow need to accomplish this two-step calculation all in one measure and avoid creating a third physical column over sales.
If we envision that our sales table contains 10 million transaction rows, the issue becomes clear. Any added column of this magnitude would weigh down the performance of our model down for our report users. It would only slow further as sales grew over time.
Row context in a measure – Best of both worlds!
At this point we ask are second question: Do I need to add filter or row context to my DAX formula? In this case the answer is yes, we need to add row context into our measure to evaluate rows.
We do this by calling on the SUMX function. SUMX is a more explicit version of SUM, in that it allows us to evaluate columns in a single row rather than just evaluating the aggregating of one column.
Here is how it works:
The SUMX function (as well as other X iterators) signal to DAX to first evaluate our expression over a table expression row-by-row, then next aggregate the resulting virtual calculated column.
The first argument in SUMX is a table expression. In our example we call the sales table to be virtually stored as our table expression in our measure.
It is worth noting that as we advance in DAX, we will learn more efficient compact ways to write this first table argument to optimize our measure. ⚡️
For our simple example we stick with the entire sales table as our first argument after SUMX as our virtual table displayed below.
The second argument in SUMX is our math expression, Unit Cost x Qty. This adds a third virtual column to our iterated rows.
In the end, our report 1. Applied filter context to the iterated rows and 2. Evaluates each individual row result (Cost x Qty) as a aggregation sum of total cost back to our report visual below.
🚨Time out for a more advanced and important nuance! 🚨
There is a small nuance between filtered rows we iterate over in the table expression, and the actual filter context (elimination of rows) we evaluate over from the visual itself.
Row context iterates over the rows visible within our SUMX table expression. However, the evaluation of our expression happens over eliminated rows in the current ‘filter context’.
Just because we filtered rows to evaluate over does not mean that we eliminated them with filter context!
We already know that CALCULATE is our magic function that intervenes with filter context that catches filters and eliminates model rows and evaluates results.
See the example below:
We can add an explicit FILTER() around our SUMX table expression to filter out rows that sell a quantity of one or less… Only orders selling two or more survive and will be iterated in this SUMX.
But if we use SUM as the expression in our SUMX, then we evaluate all rows in the ‘current filter context’ defined by the report visual. We can’t see an outer CALCULATE, but it’s there with its net over the report visual.
Our SUMX expression (SUM of Amount in this case) evaluates all rows in the current filter context as filtered by the report in the model, including those hidden orders in our table where we sold only one quantity
How does this grand total of 28 make any sense?
Not only do we get the unfiltered grand total, but we get it for each row we iterate from the table expression inside of SUMX (Unfiltered evaluated total of 14 iterated twice = 28)!
To fix this result we need to add a second CALCULATE around our SUM expression to catch the labels from our filtered table expression.
To better understand this hands on, use SUM in a calculated column after using the actual drop down table filter to reduce the rows in the table… *You are not creating filter context*, just hiding rows. Do you get the same column total? Now wrap that SUM in a CALCULATE!
This is not a real-world example by any means, but it highlights the need for CALCULATE when understanding filter context.
✅ Tip: If you simply reference a measure you have already defined, you don’t need to write CALCULATE around it. All measures by definition include a hidden CALCULATE that catches and applies all available filters regardless of context!
Other Iterators
Iterators can take the form of X iterators including SUMX, MINX, MAXX, AVERAGEX, that combine row-by-row iteration with an aggregation back to our report visuals.
Other iterators work over a table functions row-by-row, but do not result in an aggregations. These table functions are used in more complex DAX formulas. Some popular table functions include FILTER(), VALUES(), ALL(), ADDCOLUMNS(), SELECTCOLUMNS(), and TOPN().
Click here to explore DAX functions further in DAX.GUIDE
This blog post addressed how to identify and combine evaluation context with two sum measure examples. Through our examples we found that that Power BI does not stop us from adding unnecessary weight to our model, or even from writing bad measures. This highlights our need to learn and apply some very foundational DAX concepts early on in our DAX journey.
As you continue towards more advanced DAX, I encourage you to continue building your awareness of filter and row context. From there expand your experience using new DAX functions over small sample models.
The next steps in our journey are to learn to use the CALCULATE() as a developer function. As we’ve seen, CALCULATE is the most powerful and widely used function in DAX. As a developer function it is literally a Swiss Army knife that opens up endless possibilities for us.
I appreciate you taking the time to read this blog post. If you are interested in more learning opportunities, please reach out.
Thanks for reading!
– Mark
Leave a Reply to Jessica JollyCancel reply