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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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