Thinking like DAX

Published by Ahmed Oyelowo on October 29, 2020

Introduction

Struggling to write measures is usually down to one’s understanding of Filters context. This is because DAX is computed based on an Evaluation context which consists primarily of 2 contexts:

The Row Context and the Filters Context.

When creating calculated columns and calculated tables, the DAX is evaluated on each row of the table. This in simple terms is what is known as Row Context.

It is also possible to create this behavior of row by row evaluation of formulas in Measures as well. This is possible via a group of functions known as Iterators. Let’s save that for another day.

However, since Measures are primarily used in reports on visuals, it means data is being summarized.

The result of a Measure is only visible when used on a visual. What the result will be however, is dependent on the filter context the formula and the visual is subjected to. Understanding this, is an essential knowledge for any DAX author.

To practice along with this post, download the data set used here.

What is Filter Context?

Let’s come down to lay man terms. Filters in DAX and Power BI terms is about the subset of the data tables that is being selected. To put in another way, the subset of the data tables that is visible to the formula. These filters can come from:

The Visual: For example, each bar in a column chart showing sum of sales by region represents a subset of the data as the region selected.In the case of table visuals, each row on the table or categories on columns also represent filters on the data.
Slicer: Slicers are the official filtering visuals in Power BI. Any selection you have on a report page slicer contributes to the filters context.
Filters Pane: The filters Pane in Power BI can also control the filters context that determines what a visual show. One popular use of the filters pane in controlling visuals is to create Top N reports.
DAX: Yes, The Measure you write can define it’s own filters context in combination or regardless of any other filters context available in the report.This is why many experts say there are two types of Filter contexts in DAX: Internal Filters and External Filters. The Filters from DAX Measures are internal while the ones from Visuals, Slicers and Filters Pane are External Filters.

Filters Context Demo

For you to understand what Filters context is really about, and how it affects measures, you have to be able to think the way the DAX engine thinks.

To explain this concept, let’s use the simplest possible example. Sorry, it’s not “Hello World!”. It’s a SUM measure.

For a Revenue Measure written as SUM(Orders[Sales]).

On the surface, this measure interprets to The Sum of the Sales Column in the Orders Table. Right?

Let’s visualize the measure on a Matrix Table Visual

At this point, our assumption interpretation that it is the Sum of the Sales Column in the Orders Table is correct.

Now, let us add Sub Category on Rows of the Matrix Table

Wait a moment, the total value seen before has now been broken down by each Sub Category. At this point, the interpretation is still correct.

But the DAX engine now define things differently. I will talk about this in a moment.

But first, our total value of 2.297M is now broken down by Sub Category, therefore, we can say the 2.297M is being filtered by Sub Category. We can also say, the filter context on this report is Sub Category.

The 2.297M now only appear at the Total level, where no particular Sub Category is selected. Just as before we filtered the report by Sub Category.

Let’s add Region on the Columns of the Matrix Visual

The values for each Sub Category has also now been further broken down by Regions.

So the report is now being filtered by Sub Category and Region.

The Total that appears on the column for each Sub Category represents the value of the Sub Category when no Region is selected.

Do you want to think like DAX? Then, don’t see the value of 167, 380.32 as the sum of Central (33, 956.08), East (45, 033.37), South (27, 276.75) and West (61, 114.12).

True, it does add up, but that is not why DAX has 167, 380.32 there.

That value is there because it is the value of the sum of Accessories when no particular Region is selected. Just as, the value of Accessories when Central is selected is 33, 956.08.

If we include a Slicer for Segment

When nothing is selected on the slicer, all the figures remain the same. That is, no filter by Segment. If we select Home Office for example, then the report is now Filtered by Sub Category, Region and Segment.

From all the examples given above, we can summarize how the DAX engine works to provide the results seen in the visuals.

And there are two very important things to note:

Every value in the visuals is evaluated separately, based on the filter context for that value. To put in another way, in the formula SUM(Orders[Sales]), the table is Orders and the column is Sales. But for each value you see on the report, the table Orders is, first of all, based on the filter context of that value. For example, for the first value in our last example, 8, 698.53, the table Orders in the formula refers to an Orders table that has only:
1. Home Office Segment (From the Slicer)
2. Accessories Sub category (From the Table Row)
3. Central Region (From the Table Column)

All filters are activated before the formula is evaluated. The 1a, b and c filtering above will be activated before the Sales Column is summed to provide the value.

Conclusion

If you are able to think like DAX considering the two points stated above, understanding that for each value on your report visuals, the filters are different, and they are detected before the evaluation of the formula. Then you are on your way to solving more DAX Measure problems.

I’ll leave you with an assignment, to think like DAX and interpret the last value (16, 009.19), not the totals shown in the below visual, based on everything you know so far about the Filters Context.