One of the key concepts in DAX
Context transition is one of the tougher concepts to know and understand well in the DAX language. Why do you even need to know it? Well, if you want to create calculations within calculated columns that reference columns from another table or if you want to run a boolean expression within an iterator like SUMX or AVERAGEX and get a correct result, then you MUST understand what is happening with context transition or you will get lost very fast.
Try to embed these concepts into your mind
Context transition is the transformation of a row context into a filter context
You can manufacture a filter context from within a row context by using CALCULATE
Calculated columns automatically begin as a row context only
Row context does not automatically flow through relationships
Filter context does flow through relationships automatically
By invoking context transition, filter context turns on and relationships become active in the data model for that particular formula or expression
Measures referenced within other measures will always have an implied CALCULATE
A tough example to start
Let’s start with a hard example. Say we wanted to count the number of customers that had total purchases greater than the average purchased amounts across all customers. How would we do this?
You might think logically to do something like this, but unfortunately there is much more to it with DAX.
CALCULATE function is key
Ok, now let’s go backwards before we go forwards. Firstly let’s review what context transition actually is. Context transition describes the transformation of a row context to a filter context.
A row context is created in calculated columns and with iterators such as SUMX, AVERAGEX, any other ‘X’s and FILTER. The transition only occurs when a formula is contained within a CALCULATE expression (also CALCULATETABLE).
The CALCULATE function is obviously very important here. Inside any CALCULATE function there is never a row context, only filter contexts. Therefore by using CALCULATE you are able to override the row context and manufacture a filter context.
Use calculated columns to analyse behaviour
Let’s now build out a few formulas within calculated columns. We are looking here at a customer table, which has a relationship through the ‘CustomerKey’ to the sales table.
Remember with calculated columns you always begin with a row context. What if in the customer table we wanted to build a summary column which showed us the total purchases by each client. We could potentially use this calculation to group our customers into ‘top’, ‘bottom’ etc.
If you use the above formula what answer do we get? Well, not one we would ordinarily expect. You must remember though that we are currently in a row context environment because this is a calculated column. Absolutely no filters are created within a row context so there is nothing filtered at all on the sales table which is where we are referencing. Therefore all that returns is the sum of every single sale in the sales table. The same result is returned for each row of the customers table.
We need a filter context!
Now, it should become a little more obvious. What do we need?? We need a filter! How do you manufacture a filter context? Well you wrap CALCULATE around the function like so.
By adding the CALCULATE we have transitioned row context, which is automatically created as we are in a calculated column, to a filter context. Now we can also say we completed some context transition. The filter context then flows through to the sales table and we now return the total purchased subtotal for each customer.
Referenced measures contain an implied CALCULATE
We can also create context transition another way. Say we already had a measure that calculates total purchases.
We can then use this measure within the calculated column to produce the correct result.
But wait, there’s no CALCULATE to be seen? Well, whenever you reference a measure it is ALWAYS implied that a CALCULATE is wrapped around it. This is key, and actually something quite functional, as you will learn over time that it makes more complicated formulas much easier to read and write.
Context transition within measures is not so intuitive
Right, now here is where is gets a little trickier. Understanding context transition within measures.
What if we wanted to iterate through each customer is the customer table and sum up all of their purchases from the sales table. We may try something like this.
As you are now aware this is not correct. What is this actually calculating? Well, because we are in a row context (with the iterator SUMX) and a filter context was NEVER created, each row in the customers table is showing 29,358,677.22 and we are summing up this amount from every row. We have 18,484 clients in this table so the amount showing here is 29,358,677.22 * 18,484. This is obviously not correct.
Think what would a calculated column show?
How do we get what we want then. It’s exactly the same as in the calculated column. Remember the iterator has us in a row context, we need a filter context, so you need to wrap a CALCULATE around SUM(Sales[SalesAmount]).
Now we have the answer we wanted. As with the calculated column example we could also use a measure with an implied CALCULATE wrapped around it to get exactly the same result and hopefully you will agree that it reads a bit easier.
Returning to our tougher example
Let’s return to our first ‘harder’ example. What result does it actually return?
Lets walk through each calculation within the iterator COUNTAX. Firstly remember we are trying to count the amount of customers, the ‘if’ statement is attempting to work out which customers are in or out. For each row of the customer table the ‘if’ statement is checking to see if SUM(Sales[SalesAmount]) is greater than the AVERAGE(Sales[Sales Amount]). Let’s see by using calculated columns what this is calculating, as this is exactly the same as when using an iterator.
As you can see because we are in a row context (same as with an iterator) there is no filter context and therefore every row has the same total purchases and same average purchases. The formula is iterating through each row comparing if the ‘total purchases’ column is greater than the ‘average purchases’ column, which it is in every case. That means that EVERY customer will produce a TRUE result and will be counted within COUNTAX. That is why we have a calculated result of 18,484.
Don’t get caught out by DAX!
Now lets get the correct result. We need to create some filter contexts here! How do we do that? We wrap CALCULATE around the measure explicitly (actually do it) or implicitly (DAX does it for us). I’ve chosen the implicit version here.
This is actually INCORRECT! But I wanted to highlight this, as it will most likely be where you’ll get caught out time after time as it produces a result and a somewhat believable one at that. How do you know if it is even correct or not! Well currently it’s very difficult especially if you have 18K customers like we have here. The only way to know is to have a deep understanding of context transition, row context and filter context.
What is this actually calculating? The AVERAGE(Sales[SalesAmount]) is still in a row context, nothing has transitioned it to a filter context so for each row of the customer table we are getting the average sale amount across every sale ever made. This is not what we want. We actually want the average purchases made across each customer.
Learn to break out formulas into individual parts
Finally we return to our measure and put the final touches on the formula to obtain the correct result.
Now in this one formula we have 2 nested row contexts and have completed context transition three times! This for an organisational super user is as close to as hard as it gets in DAX. But at the same time is an absolute MUST KNOW if you really want to understand what is going on when writing formulas in DAX.
Check back for more example on this in the future, as most advanced DAX will contain some derivative of what is discussed in this post.