This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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 22 February 2019

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:

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:

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

Some other pages relevant to the above blog include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR

Context transition

Head office

London

Manchester