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.
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:
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?
At this point, our assumption interpretation that it is the Sum of the Sales Column in the Orders Table is correct.
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.
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.
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:
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.
The first 5 people to get this right will be given free access to my Mastering DAX Foundations Course on Udemy. I believe the answer is not that straight forward and many people will go for the same wrong answer.