As we learned in Part 1 and Part 2 in our series, data modeling in Power BI depends on clean tables and strong relationships. When it comes to extending our data model further with DAX expressions, our ability to provide new insights depends on an understanding of key concepts and how DAX expressions work along side evaluation context.
Before we start, let’s orient ourselves with key terms and how they relate to our model and report:
When I refer to the “Report”, “Reporting Canvas”, or “Report Visual”, I am referring to the blank canvas in Power BI. The canvas is where we design the report that our viewers will view and interact with.
When I refer to “The Model” or “Tables”, I am talking about the data source tables connected by relationships. Connected tables work in the background to connect, blend, and provide the structure and results that display for our viewers on the canvas on the face of reports.
It’s impossible to cover all that evaluation context and DAX has to offer here, but I do try to provide core concepts and simple examples to get the new user rolling.
I have split this into multiple posts. Post one will cover the initial basics of evaluation context along with simple DAX examples.
Our next post will contrast different approaches for calculating gross margin. By working through simple multiplication and division, the importance of both contexts will become clear.
Following the margin post, we will work on understanding how the use of CALCULATE() and Iterators (like SUMX) give us greater flexibility in writing DAX expressions, and how to avoid unexpected DAX results.
DAX, or Data Analysis eXpressions, is a functional query language designed by Microsoft. While DAX may use many of the functions we already know and love in Excel, more advanced uses include new functions and concepts that give us the ability to leverage evaluation context and blend expressions over our model.
“Evaluation Context identifies the initial environment (the target) of our expression; then works with the tables in our model to return the correct result.”
In simple terms, evaluation context asks, “Am I in a cell in the report canvas? Or am I in a column of a table from the model?” Based on the answer, the model then knows which of the two contexts, Row or Filter Context, to choose when evaluating a DAX expression.
Let’s start with how DAX Measures default to using Filter Context; then let’s learn about how Calculated Columns default to using Row Context.
A measure is a DAX expression that we write as an extension of our model. A measure by default uses filter context in its evaluation.
We don’t see the results of a measure in a physical table or cell back in the model, it just exists as an expression available for use in our report. When placed in our report visual, it somehow just works and divides out the appropriate totals! More on how this works in a bit.
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 result depends on the location of each cell or point in our report visuals. See below how when we have the context of the Name field, and how dragging the Measure in the Report Visual automatically divides out our total of $565 by each Name in the Report Visuals? This is only possible through Filter Context.
“Filter Context evaluates each DAX expression based on the location of each cell in the report. Tables are filtered back in the model to create a filtered subset of the model that match the requirements of the report cell.
Next, our DAX expression evaluates over this filtered subset to return that cells specific result. The filtered subset model ensures that the only data remaining relates to our final cell result.”
Within the visual placed on the reporting canvas, each row and column header, combined with user selected filters to help define how our tables in the back of our data model are filtered before our formulas finally evaluate a result. It’s our measure, combined with appropriate filtered tables in our model that work together to return the specific result for each cell and point in our report. As a matter of fact, everything we drag onto the reporting canvas is ultimately converted into a measure and evaluated through filter context in our report.
If we select “Mark” from our report shown below, the model (Sales Table) is filtered to only include data for “Mark”. The SUM expression then runs against this two row table, and the Sales column sums the surviving table rows ($100 and $120) returning $220 back to the report cell.
It might be difficult at first to imagine that every time a report user clicks a selection, they are filtering the model thousands of times within a blink of the eye 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.
Using the graphic below, we see that filter context is the default context used when writing measures or using the function CALCULATE. Row context is default context used when we write a basic calculated column (such as Qty x Price) in a physical table, or when we use the SUMX function in a measure.
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.
Let’s take a look at a more involved example of Filter Context.
In this example, we’ve added new tables to our model, including Customer, Time, and a Product table. If we want to evaluate the cell in our Report to return the correct Sales amount sold of Red products in Washington, we first need to filter all involved tables. We filter the Customer Table to view only customers located in Washington (Cust# 102 & 103), then we filter the Product table to show only products that are Red in color (Product Key 1). The result of the filters on these two tables flows down into the Sales table and leaves us with just two rows surviving in our Sales table. Finally, our expression does its job and totals the two rows. $185 is the final result and is returned to the original cell in our visual.
The second expression type in DAX is the calculated columns. A Calculated Column is an expression with a physical or virtual column calculated over a table row by row. Much like adding a new column in Excel, we can add a total sales column to our Sales Orders table simply by inserting a new column and multiplying quantity times unit price.
Calculated columns are tempting to use as they are easy to create and visible to the report developer, however it is not best practice. Adding additional physical columns to your model, especially if it adds millions of new cells, can add unnecessary weight and slow down model performance.
“Row Context evaluates our expression by iterating (repeating) over a table row-by-row.”
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.
In this post we’ve touched on the basic concepts of Evaluation Context, which include both Filter and Row contexts. Learning DAX requires first understanding how each context interacts with our DAX expressions. Both contexts play important roles in enriching our model with custom insights. Improving in your ability to write DAX takes a commitment to learn fundamentals, hands on practice, and gain experience troubleshooting unexpected results.
For the Excel person that pushes through this learning curve, there is light at the end of the tunnel. My recommendation is to find a good DAX book in addition to considering other learning resources to help you learn these foundational concepts.
As always, thanks for reading! – Mark
All things Power BI