Power BI works by evaluating DAX expressions over tables and columns in our model. Understanding how evaluation context and DAX interact ensures we bring dynamic and accurate results to our report users.
First, 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 visuals that our viewers will see and interact with.
When I use the term “Model” or “Tables”, I am referring to the data tables in the background that represent our star schema model. The model tables, related through relationships, provide the foundation and flow of our analysis.
In this post we are going to cover the very basics of how evaluation context (both filter and row context) combine with DAX measures to evaluate and return results to our report visuals.
Evaluation Context
Evaluation Context identifies the environment in our data model and how our expressions evaluate over our star schema model. The two evaluation contexts in DAX include filter context and row context. Under the hood, evaluation context does the heavy lifting of filtering our model and evaluating over table rows.
Evaluation context is what allows us to write one single DAX expression yet return dynamic report values based on filters driven by our visuals and chosen by our end users. This dynamic filter and expression interaction represents both simplicity and the sheer power of the Power BI product.
Two illustrations below show at the surface level how filter context filters rows over tables columns, while row context evaluate expressions row-by-row. Filter and row contexts have different purposes and generally work hand in hand returning expected results back to our visuals.
DAX Measures
DAX is a functional query language designed by Microsoft. We write DAX expressions in three ways:
1. Calculated column in a physical table,
2. Measure aggregation, or
3. A table Query that returns either a physical or calculated table.
In this post we will only focus on writing two basic measures to help explain the interaction between filter context and our DAX expressions.
Measures give us the ability to write a single dynamic expression, the visuals call on filter context to filter our model in order to produce different expression results over our report.
A measure is not materialized (likes calculated columns) within our model; measure results only appear when we drag a measure onto a visual on our report. Therefore, measures must be written to evaluate as a single result or aggregation (SUM, MIN, MAX, AVERAGE), to ensure each point and cell element in our visual contain only one value.
We use two report visuals illustrated below. The first filters our model tables to solve for a single cell representing sales of green products, and the second filters our model to solve for sales made in the state of Oregon.
Once our table rows are filtered, we refer to this filtered state as the “Current filter context”. It’s the current filter context that is passed through our DAX measure expression.
Once received by our measures, there may be further modification as defined by us in our DAX code, or the measure may simply aggregate the surviving table rows, bringing back the final aggregated sales result to our report.
In our first measure (2) we are evaluating results using the SUM function over one filtered column, this simple column measure works just fine for our report. However, in our second measure (3) for Oregon sales, it is necessary to evaluate this table in two steps. First by multiplying quantity times unit price, then aggregating this new column (Qty x Unit Price) for total sales. We can accomplish this in one of two ways. Either we:
1. Materialize a calculated column in DAX, or
2. We can use a specific function in a measure that allows us to accomplish both steps in one measure.
Let’s choose the specific measure route and use SUMX to stick with best practices and avoid materializing a new physical table column.
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 aggregate the resulting new calculated column to return the correct single value result.
This post touches on the basics of evaluation context and how to write efficient DAX measures. DAX offers straight forward options when calculating results back to our report. However, without a clear understanding of how this works in the background, we may unknowingly return inaccurate results.
Writing progressively more complex DAX requires that we are familiar with other DAX functions, how evaluation context is applied, and the interaction between both. While these concepts may seem simple at first, applying them in real life may be challenging for the DAX beginner. This learning happens over time, but for the persistent student there is a definite light at the end of the tunnel.
Thank you for reading! – Mark
Leave a Reply