Categories

Contents

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:

**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.

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*

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:**Home Office**Segment (From the Slicer)**Accessories**Sub category (From the Table Row)**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.

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.

Translate »

Scroll Up