What is Context Transition in DAX?

Understand context transition to use DAX effectively

In this video, Matt will discuss context transition, one of the most important topics to master when learning DAX. You’ll learn what it is, how it affects measures and columns, and about CALCULATE(), the main DAX function that performs context transition.

Hi, this is Matt with Playfair+. When you’re writing DAX in Power BI, you’re going to hear about context transition. So what is context transition? This video is going to help you understand the basics of it, what it is, when it happens, and how it affects your measures and calculated columns. So let’s go ahead and dive in.

Context transition. This is maybe the most difficult topic to understand, and don’t worry if you’re not understanding it right now. We will walk through it in a few examples and you’ll work through it in your own practice and that will help you understand things. So. Context transition really means when that row context becomes filter context.

So when the value of the row becomes a content filter. So both measures and calculated columns can perform those context transitions. To perform them, you need a measure that iterates across the table, using a function like SUMX(), and you need to use a measure inside the iterator. CALCULATE(). So CALCULATE() is one of the most powerful pieces of Power BI.

It allows you to perform that context transition because those iterators, like SUMX(), RANKX(), MAXX(), they, they trigger that implicit CALCULATE(). And if you want to use regular SUM(), you must wrap it in CALCULATE(), or else it will not perform the context transition. Since the measures are all evaluated in that filter context, they have that implicit CALCULATE() whether you use the function or not.

But you must use them if you don’t have, if you want to aggregate at the row level, but you’re using just something like SUM(), or AVERAGE(), those do not have implicit iterations. So that’s where you have the difference between SUM() and SUMX(), or, you know, AVERAGE() and AVERAGEX(). It’s not easy to understand, so we’ll walk through it a couple times.

The more you practice it, the easier it will be to think about, you know what? My numbers are not returning what I expected. I think I need to make sure I have a CALCULATE(), or I need to have an iterative function, for this measure so that I’m getting the right response. So, uh, it just helps you understand what’s going on behind the scenes and what Power BI is doing to your data.

Okay. So let’s talk about the CALCULATE() function. What does it do? So. Yeah, CALCULATE() and CALCULATETABLE(). Those are the only two functions that can change the filter context. So they begin with an expression. [00:03:00] You provide the filter context as shown. So this would be, I want to calculate the Sum of the Sales Amount, but I want to filter at 2018.

So what I’m doing is I’m looking at, I want to make sure I’m only returning values from 2018, and then show me the Sales Amount for that. Now, you could pull this into a table or into, you know, any kind of bar chart, line chart, add the filter to that chart, and then, you’d get the same amount if you showed Sales Amount, and then you had the filter applied.

But, what happens if you want to be able to change that in your DAX or if you wanted to do additional analysis, you want to make sure you’ve written your DAX to allow for this, instead of just relying on a filter or a slicer. Simply because those are the only other ways that you can do that filter context transition.

So, when the filter expressions are used with CALCULATE(), CALCULATE() modifies that filter context at the row level. So, we would be looking at the Sales Amount, we’re filtering down to 2018, and then we’re returning the Sum of the Sales Amount. I hope that’s starting to sink in, and I think I’ve said context maybe 50 times or more at this point.

This has been Matt with Playfair+. Thanks for watching.