The mysteries of context transition explained
Context transition happens when a DAX function creates filter context within row context, or vice versa. This blog explains what context transition is, and shows which DAX functions make use of it.

Posted by Sam Lowrie on 25 February 2019

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Context transition

Hopefully you have already read my filter context and row context blogs. Context transition is the cross-over point between the two, and is the subject of this blog!

To illustrate what context transition is, consider this small table of data:

Context transition

The Sales table above has been filtered to show just 4 rows.

If we create a new calculated column in this filtered Sales table using the formula SUM(Sales[Price]) we get this:

Context transition Filter Row Context

This might not be any surprise, as 4.75 * 4 is 19. Then again, why didn't it return the sum of the price for each individual row, rather than the sum of the prices for the whole table?

Row context lets Power BI keep track of the current row, but doesn't filter data. By contrast, filter context does filter data, as in this expression:

Calculate Filter context Row context

So what happens when we introduce CALCULATE into the mix?

CALCULATE is all about filter context: adding, amending or even removing it. Which is all well and good, but if we apply the above formula to a column in our table we get this:

Row Context Filter Context

The result returned is the SUM of only the Price in the current row. The reason for this is context transition - Power BI has transformed the row context for each row into a filter context.

This means that each row in the Sales table gets filtered to only include that row's data (and hence the Price and the SUM sale price columns give the same figures). The same thing happens with multiple tables:

Row Context Filter context context transition

Here each sale row contains the ItemID of the corresponding piece of equipment being sold. The Sales table contains 32,000 rows of transactions.

Here's what happens when we create a calculated column within the Equipment table summing all the corresponding Price rows:

Row context Filter context

The exact same problem as before: row context doesn't carry across relationships, so we need to perform context transition.

As before it is possible to use CALCULATE to create filter context using the original row context:

Row Context Filter Context

Now instead of summing all the rows in the Sales table, Power BI will use the current row as a filter. For Studded Boots this means sales which included ItemID 1 will be summed.

CALCULATE is the most useful function for causing context transition, but the most commonly used is the RELATED function:

Context transition

The RELATED function uses the existing relationship between tables to return the correct value.  This is explained below using this table as an example.

 

For SaleID 775 above the function would convert the row context of ItemID 1 into filter context through context transition, then use the existing relationship to filter the Equipment table:

Context Transition

Now the Equipment table will have the single Equipment_Type which corresponds to ItemID 1.

Used mainly within calculated columns (which don't have filter context) the result would look like this:

Context Transition

For each row the correct Equipment has been returned.

Context transition is turning the current row into a filter which can affect other tables (hence the name, as  Power BI is changing - or transitioning - row context into filter context).

This blog has 0 threads Add post