Context Transition in DAX for PowerPivot, Power BI & SSAS

Context Transition in DAX is when you transform the row context into the filter context.  In order to understand Context Transition a practical knowledge of Row and Filter context is needed, along with a practical knowledge of CALCULATE.

Understanding Context Transition is not too complicated, however it is something that DAX users get caught out on time and time again.  Therefore it is important to consider context transition when writing DAX functions.

ROW CONTEXT

Let’s start by looking at some examples to get our head around the basic concept of Context Transition in DAX.

We will use one simple table of data.  This table contains Columns for Country, Distributor, product, Colour, Qty sold and Sales price.

If we wanted to get the total sales value for each row we could add a new column.  In this column we would write the expression

Total Sales =’Sales’[Qty] * ’Sales’[Sales Price]

As this is a calculated column we know that row context is automatically applied.  So for each row of the table, DAX will get the Qty and multiple it by the Sales price at row level.

Let’s say we now add a new calculated column.  A column that will get the Gross total sales.  We could try and add the expression

Gross Total Sales =Sum(Sales[Total Sales])

With this expression we are returned a value of 69,980.50 in each row.  The reason for this we should already know, but just to recap:

The Expression is taking the entire table and adding the [Total Sales] because there is no filter context applied. As we are using a Calculated Column, only the row context applies. 

Here is what happens, on the first row DAX says to its self, for the current row, take the entire column [Total Sales] and add the values together.  It will get 69,980.5

It then moves to the next row and again it says for the current row, take the entire column [Total Sales] and add the values together.  It will get 69,980.5

This will be done for each row of the table.

Context Transition

Let’s say we want to return the total sales for that row as our Gross Total Sales. ( I don’t see the real need to do this with this data set, however for sake of example).

As the Sum function will sum in the current filter context, we need to first filter the table to just the current row, and then sum the [total sales] in the returned table.

To amend or add a filter context the CALCULATE function is used.  If we wrap our expression in CALCULATE then we will get a filtered table to carry out our SUM calculation.

Let’s create a new calculated column using the expression

=CALCULATE(SUM(Sales[Total Sales]))

It is important to note from this expression that we have not added any filters to CALCULATE.  Remember the syntax for CALCULATE is

=CALCULATE(Expression, Filter 1, Filter 2….)

In our expression all we have added is the expression.  When we use CALCULATE in this way, it works to preform context transition, transforming the row context into the Filter context.

Remember we have used a calculated column so the row context is automatic.  Here is how this new expression works

DAX says to its self ‘we want to sum the [Total sales] column’, ‘For what rows of [Total sales] do we want to sum?’, ‘well all of the rows of course because no filter has been applied’. Then DAX see CALCULATE and says ‘Oh wait, I am calculate and I can turn the current row context into a filter context’. 

FILTER Context

So DAX goes ahead and filters the Sales table to just the current row.  Now sum says, oh I only have 1 row in the current filter context, so I will sum and return that value.

Because this is a calculated column, the same expression will be carried out on each row.  Each row is converted into the current filter context, and sum will act on the current row. 

Context Transition and Measures

If we create a measure using SUMX to calculate the total sales we could use the expression

M total Sales:=sumx(Sales,Sales[Qty]*Sales[Sales Price])

But what will happen if we put this same expression into a calculated column

Well because we are looking at a calculated column, only the row context applies.  So for each row, DAX will take the Sales[Qty] for the first row and multiply this by the sales price by the first row and store the value, it will then do the same for each row of the table.  When the value of Sales[Qty]*Sales[Sales Price]  has been calculated for each row, SUMX will then add all of these values together.  DAX will then go to the second and subsequent row of the table and do the same thing.

If we take our measure M total Sales and put this into a calculated column, what do you think will happen?  Remember the measure contains the same expressions as the calculated column SUMX Total sales.

When you create a measure, DAX automatically wraps a CALCULATE around it.  So although you cannot see CALCULATE in the calculated column, by virtue of the fact you have placed a measure in a calculated column, CALCULATE is present.

Behind the scenes the Expression look like

=CALCULATE(sumx(Sales,Sales[Qty]*Sales[Sales Price]))

As there are no filters placed within CALCULATE, CALCULATE works to preform context transition, converting the row context into a filter context.

Therefore we are returned the total sales for the row in question.

The examples that I have shown here are very simple because I have used only one table of data.  But as you all know, things are very really this simple.

When you are working with multiple tables of data you must remember that the filter context propagates down the many to one relationships in your data set.

This means that if you use CALCULATE to preform context transition, all the related tables going from the many to one side, will also be filtered.

Here are the key concepts of Context Transition in DAX to understand

• Calculated columns work at row level
• To switch from row level to filter level is known as context transition
• CALCULATE is used to preform context transition by only including an expression within CALCULATE
• Once Context Transition occurs, a table is returned and the expression is carried out on the returned table.
• Filters propagate down the many to one side of relationships in your data set
• Measures include an applied CALCULATE wrapped around the expression 

In a future blog post I will cover this more complicated scenario with an example, as fully understanding Context Transition in DAX.  Sign up to my newsletter so you don’t miss out.

Sign up for my newsletter – Don’t worry, I wont spam. Just useful Excel and Power BI tips and tricks to your inbox

SIGN UP NOW

In return for this Tip –Context Transition in DAX for PowerPivot, Power BI & SSAS – I ask that you share this post with your friends and colleagues

---

Cross posted from my blog with SteemPress : http://theexcelclub.com/context-transition-dax-powerpivot-power-bi-ssas/

---