skip to Main Content

my house Fairbanks, Alaska

Figure 1 View outside of my home office window May 2nd, 2018 Fairbanks, Alaska.

Intro

Hello, my name is Matthew Brice and I am guest broadcasting from my home office (via tape delay) in Fairbanks, Alaska.  PPPro put out a call for guest bloggers and they were kind enough to allow me to provide some content.  It has been a long time putting it together as I started in May as the pic above suggests.    Have no fear as the snow did eventually melt.

The power in Power BI comes from the Dax language. I read a lot of forum posts on both the Power BI community and Mr. Excel sites, and in my observation, it seems that beginners don’t fully grasp the core concept on which Dax is built. This post is my attempt to explain it my way.

At its core, all of Dax can be summed up in one sentence:

Dax is about manipulating the values that are
filtering columns in the data model.

That’s it. Every function we write in Dax is used to change these values. Now, while the concept is simple, writing the code requires an in-depth understanding of the nuances of Dax (which is beyond the scope of one blog post).

Here I will try to provide a 10,000-foot view of Dax to aid in seeing the forest through the trees, and hopefully as a useful reference to guide your way.

Filter Context

For me, whenever I am new to a concept, and I read a statement like the one above, I often say “Well that’s great, but just what does that mean?” I am a big believer in mental models or ways of visualizing the flow of execution. As most are aware, all the values filtering columns are collectively known as the filter context[1]. I visualize filter context as a separate box that sits “on top” of the tables in the data model. We then use Dax to add/remove/change the values in the box before being applied to the model and computing our value. Simple right? To make the example clear, we will only talk about a very straightforward model (in fact just a single table). But the concept is the same no matter how many tables are in the model.

*The trick with the larger models is following the flow across relationships.

Here is our raw dataset:

Raw dataset

Figure 2  Raw Table Data

Below I have Table 1 as a pivot table.

Pivot Table Table 1

Table 1 Raw Data

The yellow highlighted value shown has the filter context in my filter context box.

Where “Sum of Amount” is a simple :

Sum of Amount := SUM ( Table1[Amount] )

To arrive at the value of 100, we sum up all the values in the Amount column where Person = “Anne,” Color = “Blue” and Type = “Bike.” When we filter the data source table with these values, we are left with a single row to sum. We see that our expected value of 100 is returned in the pivot.

Anne Blue Bike

That is simple enough, but what if we want to do a more complicated calculation?  To do that we must dive into functions that add/remove/change values in our filter context box.

CALCULATE

The most common and arguably the most important function in Dax is CALCULATE.

It’s function reference is:

CALCULATE(<expression>,<filter1>,<filter2>…)

 

CALCULATE is somewhat unique in that it evaluates the 2nd, 3rd, …nth parameter first, and evaluates the first parameter last using values from my Filter Context Box. I think it is extremely helpful to list briefly the steps CALCULATE performs whenever it is invoked. (So maybe we are not at 10,000 feet, but 5,000?)

The CALCULATE function performs the following operations:

  1. Create a new filter context by cloning the existing one. (***Important visual step!***)
  2. Move rows in the row context to the new clone filter context box one by one replacing filters if it references the same column. (We will ignore this step for this post)
  3. Evaluate each filter argument to CALCULATE in the old filter context and then add column filters to the new clone filter context box one by one, replacing column filters if it references the same column.
  4. Evaluate the first argument in the newly constructed filter context.
  5. Destroy this newly created, cloned filter context box before moving on to calculating the next “cell.”

It is much easier to visualize with an example:

So suppose we wanted to ignore the color of the item when summing up the values.  In Dax we would write the following measure:

Sum of Amount No Color =
CALCULATE ( SUM ( Table1[Amount] )ALL ( Table1[Color] ) )

This measure reads “Sum the values according to the Filter context box, but first remove any filters on the column Table1[Color]”.

Let’s look at the result first, then step through how the Dax engine calculated the value.

As you can see here, the highlighted field is 400.

Sum of Amount no color

1. Clone the existing filter context (Edit/Copy/Paste if you will):clone filter context

 

 

2. Move Row context into Filter context (ignore for this post)

3. Evaluate CALCULATE parameters. In this case, our only parameter is ALL ( Table1[Color] ) which removes any filter on the ‘Color’ column, so our Filter Context box is now this: Filter Context Box - Table 1 Color

 

 

4. Evaluate the first parameter with our Cloned Filter Context box. When you apply the Person = “Anne” and Type = “Bike,” the remaining rows to sum up look like this and voila the sum of the rows is 400.

Anne bike

Note: Remember, the preceding steps were only to calculate the highlighted cell.  Once completed, the Dax engine logically moves to the next cell in the pivot and goes through the algorithm again.

5. Destroy the Cloned Filter Context Box.

It’s simple once you understand the steps! 

Now let’s create a new measure with an added parameter.

Sum of Amount No Type Ben only: =
CALCULATE (
    SUM ( Table1[Amount] ),
    ALL ( Table1[Type] ),
Table1[Person] = “Ben”
)

 This measure reads “Sum the values according to the Filter context box, but remove any filters on the column Table1[Type] and only keeps the rows where the column Table1[Person] = “Ben”.”

Again, we will look at the strange looking result first and then step through how the Dax Engine computed the value.

Pivot - All Ben

Once again we use the CALCULATE algorithm:

 

  1. Clone the existing filter contextClone Filter Context
  2. Move Row context into Filter context (ignore for this post)
  3. Evaluate CALCULATE parameters.  Since we have two parameters, we have two steps to do:
    1. ALL(Table1[Type] ) This removes any filters on the Table1[Type] column so now the Cloned Context Filter Box looks like this:remove filters type
    2. Table1[Person] = “Ben”[1]. This step says to remove any existing filters on the column Table1[Person] and  then add the filter “Ben.” After doing so, our Cloned Filter Context Box looks like this.Filter Context - Add Ben

 

 

4. Evaluate the first parameter with our cloned Filter Context. When you apply the Person = “Ben” and Color = “Green,” there remains only one row, and so the value returned is 400.

Filter - Ben - Green

5. Destroy the Cloned Filter Context Box.

So even though on the pivot it shows “Anne,” “Green” and “Bike,” we used Dax to change the column filters to return the sum of the values for “Ben” & “Green” only.

Finally, for our last example we’ll change the CALCULATE statement to be like so:

Sum of Amount Green only :=
CALCULATE ( SUM ( Table1[Amount] ), FILTER ( Table1, Table1[Color] = “Green” ) )

Here is what the table looks like with this measure added:

 

Sum Green only

 

So for the highlighted cell, we’ll do the same drill as the last two examples:

  1. Clone the existing filter contextClone Filter Context
  2. Move Row context to Filter context (ignore for this post)
  3. Evaluate CALCULATE parameters.  In this case, our only parameter is FILTER (Table1, Table1[Color] = “Green”)  FILTER is an iterator that scans the table listed as the first parameter (Table1) and keeps only the rows where Color = “Green.”  So our new Cloned Filter Context Box looks like this:new cloned filter context
  4.   Evaluate the first parameter with our cloned Filter Context.  When we apply the filter Person = “Anne” and Color = “Red” AND Color = “Green” (meaning each row in Table1 must have a case where Color is Red and Green at the same time) and Type = “Bike”  we run into an issue.   Since no row can be labeled with two different colors at the same time, our FILTER function returns an empty table, and our highlighted cell is blank.
  5. Destroy the Cloned Filter Context Box

Conclusion

After this last example, I hope it is obvious why rows that already have a Color = “Green” do show a value.

Here is what the Cloned Filter Context Box looks like for cells with a value in them:  So color needs to be “Green” and “Green” at the same time – no problem. cloned filter context green and green

 

 

Why did my last example not replace the existing filter (Red) on the Color column for the highlighted cell?  The logical answer is because per the CALCULATE algorithm:  “Evaluate each filter argument to CALCULATE in the old filter context.”  This means Table1 that is scanned by FILTER in my measure is already pre-filtered down to where Person = “Anne,” Color = “Red” and Type = “Bike.”   It then further reduces the found set of rows where Color = “Green” ( returning an empty table).

My mental model step #3 logically reaches the same result albeit in not 100% the same way.  I hope this is not confusing(?).

In conclusion, I see people dive into trying to solve a problem while losing sight of this simple fact:

Based on the desired output we will use Dax to change the values that are filtering the columns

For me, having a mental model aides in my understanding and I have attempted to show you mine. I hope this is of use to you.  Post any questions or corrections in the comments below,  and finally…

you got this

 

[1] Filter Context is one of two “contexts” in Dax. The other is Row context which while equally important, will not be discussed in this post.

[2] This clause is actually Dax shorthand for this code: FILTER ( ALL ( Table1[Person] ), Table1[Person] = “Ben” )

Where It’s At:  The Intersection of Biz, Human, and Tech*

We “give away” business-value-creating and escape-the-box-inspiring content like this article in part to show you that we’re not your average “tools” consulting firm. We’re sharp on the toolset for sure, but also on what makes businesses AND human beings “go.”

In three days’ time imagine what we can do for your bottom line. You should seriously consider finding out 🙂

* – unless, of course, you have two turntables and a microphone.  We hear a lot of things are also located there.

Matthew Brice

My name is Matthew Brice. I was born, raised, and currently reside in Fairbanks, Alaska. I was raised in the family heavy civil construction company that performed projects almost exclusively in the remote parts of Alaska; places you either have to fly, barge or go overland in the winter to get to. Like all my relatives in the company, I spent around 10 years as a heavy equipment operator and truck driver before moving into the office and eventually becoming CFO. My degree is in Business Management/Accounting with a little bit of tax sprinkled in. In 2010 my family sold our collective business lines to Calista Corporation, a regional Alaskan Native corporation. My current role is IT Director for a subsidiary of Calista known as Bilista Holding, LLC. My wife and I have two young boys, two English bulldogs, two rabbits, four cockatiels, Silkie/Frizzle/Sizzle chickens and other animals I'm sure I have temporarily forgotten.

I was also the office geek and have gotten certified (though all are expired) in A+, Network +, the first part of CCNA (ICND1) as well as completing a large portion of the coursework towards a masters in CS - until I realized I didn't want to be a software engineer.

I got started working with Power Pivot in 2013 when I was quite frustrated with the current financial reporting tool we were still running (FRx) and was looking around for something better when one night I stumbled upon Power Query / Power Pivot. I could immediately see the potential so I bought Rob's first book, read the whole thing in one night and went back to the office and quickly knocked out some reports. I even managed to impress myself with what could be done with a limited knowledge of the product. I have since invested quite a bit of time learning the nuances of Dax and attended a Marco Russo workshop last year. For my purposes Power Query/Power Pivot is hands down the best reporting combo I have used. Thanks for reading.

This Post Has 20 Comments
  1. Thanks so much for this post! This is a concept I continue to struggle to fully understand. I was following all the way down until the last example. I still can’t fully wrap my mind around why in the second example, Table1[Person] = “Ben” removed/replaced the existing filter (Table1[Person] = “Anne”), whereas in the third example, FILTER (Table1, Table1[Color] = “Green”) did not remove the existing Table1[Color] = “Red” filter but instead created an “and” filter context.

    1. This is one of those things that you have to just “know” about Dax.
      CALCULATE (
      SUM ( Table1[Amount] ),
      ALL ( Table1[Type] ),
      Table1[Person] = “Ben”)

      is the second example code. A simple expression of Table1[Person] = “Ben” is called a boolean expression – it’s either true or false. But what you have to keep in mind is it is just Dax shorthand that the engine internally rewrites to :
      CALCULATE (
      SUM ( Table1[Amount] ),
      ALL ( Table1[Type] ),
      FILTER ( ALL ( Table1[Person] ), Table1[Person] = “Ben” ) )

      The ALL in this case ignores any filters on the ‘Person’ column and therefore returns all the values of the ‘Person’ column; effectively replacing for my highlighted value the filter ‘Anne’ with ‘Ben’ (As it will for every row in the table. No matter what ‘Person’ is on the row in visual, it will get replaced with ‘Ben’ – and to be technically precise even ‘Ben’ in visual is replaced with ‘Ben’ from Dax code. But are the same value so results are as expected.) Compare that to my third example which is :
      CALCULATE (
      SUM ( Table1[Amount] ),
      FILTER ( Table1, Table1[Color] = “Green” ) )

      In this case the argument to CALCULATE is a full expression, but note the most important part: it does not use the ALL function. So FILTER then iterates over Table1 which is retrieved using the “old” filter context, meaning it was already pre-filtered down to rows with ‘Anne’ , ‘Red’, ‘Bike’. The FILTER function then further filters down this pre-filtered table to ‘[Color] = “Green”‘. Since no rows in pre-filtered table are ‘Green’, FILTER returns a blank table to CALCULATE, so CALCULATE returns a BLANK value. So the difference between the two examples is the hidden ALL. By trying to shorten the code, the Dax developers made it a bit harder to understand what is going on. But this is not the only place you’ll run into hidden functions. Make sense? Clear as mud?

  2. Great explanation, in particular around the effect of using FILTER or not in the CALCULATE – and very timely for me as was starting to troubleshoot two formulas that were producing incorrect result due to this exact aspect, I had not used FILTER. Thanks

    1. Yes – that was something I considered while drafting the post: how far down the rabbit hole should I go? There are many nuances in DAX, and a Boolean expression used as a filter argument to CALCULATE is one of them. You have to remember that Table[Color] = “Red” is internally expanded by Dax engine to : FILTER ( ALL ( Table[Color]), Table[Color] = “Red” ). This quasi-hidden use of ALL has messed up a lot of people…including me early on.

  3. Could you please provide a scenario to demonstrate the case how the step 2 “Move Row context to Filter context” work? Thanks.

    1. The concept of Row Context really needs to be its own post. I don’t think I can adequately explain it in the comments section. But it is a vitally important concept IMO to understand because it can rear its head in places and cause results that at first glance appear to make no sense. I see that done all the time. If PPPro permits, I’ll work on a post about it. Stay tuned(?).

  4. The Filter Context is just a replica of the Criteria Range of an Advanced Filter (which in turn emulates the WHERE clause of a SQL Query)
    Now the only thing to teach in CALCULATE to an excel user is the Blocking semantics of Calculate where in CALCULATE wins in case of Conflicts (two filters on the same field one from pivot and one from CALCULATE)
    The next is the concept of Context transition where in a ROW context converted to a FILTER Context when CALCULATE is used inside a calculated field
    And Finally the Measure [mSales] defined as =SUM(DATA[ACTUALS]) is actually CALCULATE(SUM(DATA[ACTUALS]))
    So when a measure name is used on the right hand side of an equals operator in a Filter Argument of the FILTER function – Context transition happens

    1. “Now the only thing to teach in CALCULATE to an excel user is the Blocking semantics of Calculate where in CALCULATE wins in case of Conflicts (two filters on the same field one from pivot and one from CALCULATE)” This is what I was trying to convey in my post but perhaps I missed the mark a bit. And to be more precise, it is not CALCULATE that performs blocking semantics (or overwrite is now the preferred terminology?), but rather some other function used as a filter argument to CALCULATE. For example, CALCULATE ( SUM ( Table1[Amount] ), ALL( Table1[Color] ) ) does perform blocking on the column Table1[Color] due to the use of ALL, not because the column was referenced in CALCULATE. However, if we do: CALCULATE ( SUM ( Table1[Amount] ), FILTER ( VALUES ( Table1[Color] ) , Table1[Color] = “Green” ) ) no blocking semantics are performed because neither FILTER nor VALUES perform blocking.
      And yes Row Context Transition is another vital aspect of Dax that people need to understand. I skipped it in this post because it is too big a topic to mash together with this one. And yes when used on the right side of a equals operator context transition may happen depending on circumstance. But context transition also happens without a CALCULATE via all the table functions such as FIRSTDATE, LASTNONBLANK, etc. It’s the combo of an iterator & CALCULATE/Table Function that triggers context transition. But good point.
      Thanks for commenting.

  5. Two questions on this excellent article:
    1) In the “Sum of Amount No Type Ben Only” measure you say “This measure reads “Sum the values according to the Filter context box, but remove any filters on the column Table1[Type] and only keeps the rows where the column Table1[Person] = “Ben”.”

    But it doesn’t do that – keep rows where the column is Ben. It *replaces* everything in the Person column with Ben. So in the first row, it says “Ok, this is Anne, blue, and bike, but I’ll replace Anne with Ben, and get all types” so it ads up uses Ben and Blue. Right? Or am I not understanding what you are saying?

    2) Related to #1, why would you write a measure like this? After seeing your example, I am having trouble figuring out where I’d want that behavior. Your article would now make me more inclinded to write:

    Total Ben Only =
    CALCULATE(
    [Total Amount],
    ALL(‘Test Data'[Type]),
    FILTER(
    ‘Test Data’,
    ‘Test Data'[Person] = “Ben”
    )
    )

    This gives blank values for all of Anne’s and Charlie’s, and fills in Ben’s. Much like your other FILTER() example.

    Am I misunderstanding something, and can you give an example of where you’d want the results of your Ben Only measure?

    1. 1) After reviewing your comment and my post I can see your point that perhaps it was a poor choice of words on my part. A boolean expression used as a filter argument like Table1[Person] = “Ben” is just Dax shorthand that the Dax engine internally rewrites to: FILTER ( ALL ( Table1[Person] ), Table1[Person] = “Ben” ). The use of ‘ALL’ does return all values for the ‘Person’ column in the table ‘Table1’, and then the FILTER iterates over the list of values and only keeps ‘Ben’. So you are correct that effectively it does replace ‘Anne’ with ‘Ben’ in your scenario and will sum and display the amount column for all rows where it is true that ‘Person = “Ben”‘ and ‘Color = “Blue”‘ even though the pivot shows ‘Anne’, ‘Blue’, and ‘Bike’ for the respective row.
      2) The intent of the examples was to explain the algorithmic logic of the Dax engine, not to resolve any particular problem so I wasn’t trying to solve for any particular real world use scenario. As I was writing the post I also thought of many other examples I though to include to shed light on other nuances of Dax, but I came to the realization that the post would be too long so I “settled” for three.
      ok?

      1. Thanks Matthew. I wasn’t meaning to challenge “why would you do this” but was wondering if this is how a raw filter in CALCULATE works, I am having a difficult time coming up with a reason to use one. The results of the FILTER() filter is what I would always want – I think.

        Again, appreciate the article. Can never read enough in understanding the nuances that filter context and CALCULATE() has. 🙂

        1. No problem. I didn’t feel challenged at all.

          And “always” is a long time… I use the shorthand notation all the time.

          Thanks for commenting.

  6. If I understand Edh’s question correctly, it’s about the use of a raw CALCULATE filter like this:
    CALCULATE (SUM (Table1[Amount]), Table1[Person] = “Ben”))
    instead of the Filter version like this:
    CALCULATE (SUM (Table1[Amount]), FILTER(ALL(Table1[Person]),Table1[Person] = “Ben”)),
    If I’ve not misunderstood the issue, then according to Rob Collie’s PP book (2nd Edition), it has to do with performance, as well as some safety in keeping early users of DAX out of trouble. If you can use the simpler ‘shorthand’ of the Boolean version, then apparently it will be faster (if there is any difference). The equivalence is shown on page 244 of Rob’s book, and the performance concern is mentioned on page 172. Maybe this helps in understanding why you might want to use the shorthand or raw Boolean version of the expression.

    BTW, excellent post. The visual model was very helpful.

    1. Errr….sorry…but no. There is no performance difference between the two measures you have listed because the query plans are the same. The Dax engine always rewrites the former into the latter. The only advantages are code that is more compact / easier to read and less keystrokes for the author. Thanks.

Leave a Comment or Question