Understanding context transition

[hohlick]

Hi All!

I have two tables, 'report' and 'pc'.
Lookup table 'report' has 3 columns, [app], [line], and [site]
Fact table 'pc' also has 3 columns, [pc_id], [site] and [engine]
Tables linked from 'report' (one side) to 'pc' (many side) by [site]

So I am adding a calculated column (as a test before using X-function) in 'report' with next formula:

Column1 =
returns desired result: a number of rows in 'pc' where 'pc'[engine]="a" and 'pc'[site] = report[site]

Ok, if I replace last formula with
(which should be an equivalent to previous?) and it also gives me a correct result.

But if I use next syntax:
Column2 =
this columns gives me an equal result for each row, exactly a number of rows in 'pc' where [engine]="a", doesnt matter what is the current row context.

So, as far as I understand context transition, CALCULATE applies context transition, then it calculates FILTER part, and FILTER part can overrides that transited context, and then calculation performed.

In Column1 filter part of CALCULATE did not cleared filter from pc[site], then thats why we got correct results.
In Column2 filter part got entire 'pc' as source table for iteration (as there are no outer filters for 'pc', and it acts like ALL('pc') ) and then applies 'pc'[engine] = "a" and thats all.

Ok, but here goes the question (actually two):

1. Why gives me the same result as a count of rows in 'pc' depending on current row in 'report'? I'm cleared filters from pc[site], there are no other filters applied, so why context transition acts?

2. When is the filter part of CALCULATE will not override filter from context transition?

Thanks!

[MattAllington]

You are asking great questions, and that shows you are well on the way to becoming a DAX ninja. This is a very hard topic with layers of complexity and your knowledge is already very good (better than most). You simply can't hope to absorb it all at once, and learning incrementally (like you are doing) is the only practical way forward.

Let me address the points in order they come up in your post.

Note you can download my file below and refer to it as you go.

A. You are using a Calc column before moving to SUMX.
EXCELLENT! Calc columns are much easier to learn because you can see the results. This is a great practice.

B. There is a bit happening here. Firstly let's look at your first two formulas.

Formula 1


Formula 2



Formula 1 above is the simplified syntax of formula 2 (syntax sugar). The dev team built this so it is easier to apply "simple" filters inside CALCULATE without having to learn the FILTER function. If you think about it, if the semantic equivalent did not contain the ALL(table[column]) statement, then the formula would not work at all - because it could be applying a new filter on top of existing filters on the same columns. eg If there were a filter on Category="bikes" and then you applied a new filter in CALCULATE on category="clothing", they would work against each other and return nothing. It only makes sense if you first remove the filter on the table[column] first and then reapply the filter you want.

C. Your next formula is slightly different again - it has no ALL statement. So your formula iterates over the pc table while retaining the current filter context.
Formula 3


My next formula that I wrote removes the formulas from all columns. This formula iterates over the pc table with all filters in the current context removed.
Formula 3.1


So you are applying a new filter on top of the existing filters (if any), where as I am removing the existing filters (if any) first. But both these formulas return the same result. So this implies that there is nothing filtered in the current context - otherwise they should return different results, right? The key question here therefore is "what filters currently exist in the filter context"?

D.

So, as far as I understand context transition, CALCULATE applies context transition, then it calculates FILTER part, and FILTER part can overrides that transited context, and then calculation performed.

Yes and no. This is the source of the confusion (and indeed it can be confusing).

In formulas 2 and 3.1, the ALL statement is effectively removing the filters from the current filter context in the column and table respectively before the iteration starts. In your formula 3 there is no such ALL statement, so the filters in the current filter context are not removed - instead the FILTER portion of the formula is iterating over the entire PC table. But because the second parameter of CALCULATE gets executed first (regardless if ALL exists as the table argument), the filter test is executed in the current filter context.

edit (to avoid confusion): Here comes the key point: => No context transition is affecting any of the internal filters in any of these formulas (2, 3, or 3.1 ). There is context transition, just that it comes AFTER the formulas are resolved. The easiest way to see what is happening here is to take your formula 3 and wrap it inside another calculate

Formula 4

Now context transition occurs first (thanks to the outer CALCULATE) and then the FILTER portion of the inner CALCULATE iterates over a filtered version of PC (filtered by context transition) and you get what you expect. Nested CALCULATE functions always work from the outside in and the second parameter (if there is one) always gets executed first.

Formula 2 returns what looks like context transition, but it is actually a filter propagation masquerading as context transition. The table iterated by FILTER in formula 2 is the virtual table ALL(pc[engine]) - similar to VALUES(pc[engine]). When this filtered virtual table ALL(pc[engine]) is passed as the table parameter inside the FILTER function (as in formula 2), FILTER first works out which rows to keep in the virtual table, then CALCULATE takes that filtered virtual table and before doing the calculation, it propagates the filters from the new virtual table to the real pc table, and then finally it does the calculation countrows(pc). So it is this filter propagation between the filtered copy of pc[engine] that is causing the filtering effect, not context transition. By definition the only rows that will show a value in this column are those where pc[engine] = "a".

Formulas 3 and 3.1 are doing the identical task (although they are different formulas). They are both iterating over an unfiltered version of the pc table because there never was any filtering on the table in the first place (there is no context transition). Formula 3.1 first removes existing filters with an ALL function, but there are none anyway, so both formulas return the same result - the full count of rows for the entire table. Of course if there were initial filters over the pc table in the current filter context, the results would be different.

Formula 4 is doing something different - it is iterating over an unfiltered copy of pc[engine] which is a virtual table. There is only 1 row where pc[engine]="a" in this virtual table, and hence the table returned by FILTER is a single row, single column table with one value (pc[engine]="a"). Very importantly, this filtered virtual table retains a relationship to the pc table, and this relationship can and will be called on by CALCULATE. This virtual one column, one row table is passed as a table filter to CALCULATE, then CALCULATE tells the engine to propagate the filters and the virtual table then filters the real pc table so that only rows were pc[engine] = "a" remain in the pc table. The result is the same column as formula 4. But understand well that formula 4 got to the end state via context transition (the outer calculate) but formula 2 got there by way of filter propagation inside the CALCULATE from a virtual table, NOT by context transition.

Your last question

The ALL() natively Inside a calculate operating as a stand alone filter (ie not nested in a FILTER statement) operates differently. In this case, all is removing the table from the filter context entirely (Read The Definitive Guide to DAX, Page 326 http://xbi.com.au/tdgtd). This is technically different from iterating over an unfiltered copy of the table.

If you want to work through with my formulas, here is the file I used

context transition.xlsx

(205.4 KiB) Downloaded 189 times

[MattAllington]

Here is another article that I wrote that covers a similar topic http://exceleratorbi.com.au/double-calc ... x-problem/

[hohlick]

You are asking great questions, and that shows you are well on the way to becoming a DAX ninja. This is a very hard topic with layers of complexity and your knowledge is already very good (better than most). You simply can't hope to absorb it all at once, and learning incrementally (like you are doing) is the only practical way forward.

Thanks Matt, this is very kind words for me, as you are one of my (virtual, to my regret) teachers and your book helps a lot.
Also a HUGE thanks for your extended reply.

C. Your next formula is slightly different again - it has no ALL statement. So your formula iterates over the pc table while retaining the current filter context.
Formula 3

...
The key question here therefore is "what filters currently exist in the filter context"?

Sure. But there are no outer filter context in calculated column, correct? So Formula 3 got 'pc' without any filters applied, as if it is ALL('pc')

D.

So, as far as I understand context transition, CALCULATE applies context transition, then it calculates FILTER part, and FILTER part can overrides that transited context, and then calculation performed.

Yes and no. This is the source of the confusion (and indeed it can be confusing).

Specially in the case when Alberto and Marco write this in the "Understanding evaluation order of context transition" chapter of TDGTD:

...there is an order of precedence between the filter context created by context transition and the filter context created by conditions in CALCULATE. CALCULATE executes context transition before, and it applies the filters later. Thus, any of the conditions of CALCULATE can override the filter created by the context transition.
... filters in CALCULATE overwrite filters coming from context transition (in other words, filter arguments are applied later).

and a little bit later:

There is a precedence order between context transition and filter arguments of CALCULATE. CALCULATE applies the filters after it has performed context transition. Thus, filters can override the context created by the transition.

and this is very confusing when I read next:

Here comes the key point: => There is no context transition in any of these formulas (2, 3, or 3.1 ).

I understand this in a little bit other words, may be wrong:

1. Context transiton affects only the "expression" part of CALCULATE.
2. Filter part of CALCULATE (raw filter or FILTER or other table filter) operates in outer (towards CALCULATE) filter context. It means that if we use raw filter, we actually rewrite filter context to explicitly named columns (because of hidden FILTER(ALL(column),...). If we use FILTER, then "table" argument of FILTER goes from outer filter context, then it could be modified by ALL etc.
3. So if there is outer CALCULATE, it performs context transition to nested CALCULATE, and then filter part of latter get filtered table/column for iteration. Because all nested CALCULATE (both expression and filters part) is an "expression" part of outer CALCULATE
4. As there are no outer filters in calculated column, then FILTER gets all table provided as its first argument.

And there is the catch that I cannot resolve:
on page 184 in "Definitive guide to DAX" I see this formula for calculated column:

and in its description the Italians wrote:

ALLEXCEPT removes all the filters from the table, except for the columns specified in its arguments. Because we use ALLEXCEPT on Product, apart from the product category, this means that all the filters coming from the context transition will be removed, keeping only the one on ProductCategory. In other words, we are asking to compute the sum of total profit for all the products with the same category as the current one.

Wait, what context transition? ALLEXCEPT is a filter part of a CALCULATE, so why is transition happens there?

So, this is a special behavior of ALLEXCEPT which looks for outer row context outside of CALCULATE (like RELATEDTABLE as first argument to FILTER) or it is only FILTER function that ignores context transition?.. Then mindblow comes.
Ok then, where I am wrong?

I also see this in the post you referred:

the FILTER portion ... is executed in the initial filter context without any context transition. The CALCULATE isn’t executed until after the FILTER portion is complete. That is why CALCULATE in this case does not force Context Transition ...

Formula 2 returns what looks like context transition, but it is actually a filter propagation masquerading as context transition. The table iterated by FILTER in formula 2 is the virtual table ALL(pc[engine]) - similar to VALUES(pc[engine]).

That! I remember your expanation about virtual tables relation to static tables from your book and posts, but "ALL(pc[engine]) - similar to VALUES(pc[engine])" was not clear. Nice

When this filtered virtual table ALL(pc[engine]) is passed as the table parameter inside the FILTER function (as in formula 2), FILTER first works out which rows to keep in the virtual table,
then CALCULATE takes that filtered virtual table and before doing the calculation, it propagates the filters from the new virtual table to the real pc table, and then finally it does the calculation countrows(pc). So it is this filter propagation between the filtered copy of pc[engine] that is causing the filtering effect, not context transition. By definition the only rows that will show a value in this column are those where pc[engine] = "a".

so, how much rows comes here to the FILTER as a table argument? In other words, what it the table under ALL(pc[engine])? A one-column table of the distinct values in pc[engine] column? If yes, then FILTER iterates over all these values, select only rows with [engine]="a", then, as you described below, CALCULATE got a one-row-one-column table for filter propagation on 'pc' table. And in 'pc' table (filtered this way) then ALL rows with [engine]="a" will remain? If yes, there have to be rows with any [site] corresponding with [engine]="a". In this case COUNTROWS(pc) will calculate the same amount of rows for each row in 'report' table. But it doesn't, it looks like context transition was also taken in evaluation context, and these filters (from FILTER and from transition) performed as if there was AND clause.

As I can see there, FILTER result is all rows from a table that is the result of ALL('pc'[engine]), where [engine]="a". May be ALL('pc'[engine]) also looking for outer context, got a part of 'pc' table filtered by current row context, then removes only filter placed on 'pc'[engine] (but not on pc[site] which is linked column to report)?

Or there more feasible option - I do not understand of what is the parts of filter, propagated by CALCULATE to 'pc' from virtual table.

The ALL() natively Inside a calculate operating as a stand alone filter (ie not nested in a FILTER statement) operates differently. In this case, all is removing the table from the filter context entirely (Read The Definitive Guide to DAX, Page 326 http://xbi.com.au/tdgtd). This is technically different from iterating over an unfiltered copy of the table.

Wait-wait-wait. This is totally unclear:
If standalone ALL(pc[site]) removes entire 'pc' table from filter context, then there should be all rows from 'pc' table for COUNTROWS. But they still filtered by row context!
Could you please refer to the chapter name in TDGTD where Italian guys wrote about this? Now I can only see a copy from Google Books, and it seems like there no page numbers.

If you want to work through with my formulas, here is the file I used context transition.xlsx

There some problems with Excel opening your file on this computer (it happens there with downloaded files), I'll try to look at it later on other computer. Thanks a lot!

I added a file to this message,where I repeat your four formulas and added 3 more:

Formula 5:

Formula 5.1:

These two formulas have the same result for each row in report: number of related (to current row in 'report') rows in 'pc'. In other words, looks ALL(pc[site]) is totally ignored?
And Formula 6:

This formula gives me the same result as formulas 1, 2 and 4 from your post: number of "a" depending on current [site] in 'report'. Here first filter AND second filter - ok, got it.

This still confusing...

Thanks again, Matt!

Regards,
Maxim Zelensky

[MattAllington]

Ahhh, hi Maxim - I know the twitter profile well. . Welcome to the forum.

Thanks for asking your follow up questions as it really helps me understand which parts are not clear. I will improve the explanation when I turn this into a blog post later. And probably most importantly you have helped me realise I made a few mistakes (whoops) - sorry about that. This is definitely preventing you getting it all straight in your mind.

Sure. But there are no outer filter context in calculated column, correct? So Formula 3 got 'pc' without any filters applied, as if it is ALL('pc')

.

Yes, correct. I should have stated that for clarity. But my statement is still true and I encourage you to always think about these things as being "in the current filter context", even if the filter context is empty (as in is case). Always say to yourself " go do this in the current filter context" as it helps you think through "what is the current filter context"

Specially in the case when Alberto and Marco write this in the "Understanding evaluation order of context transition" chapter of TDGTD:

I understand how this creates confusion. They explain it correctly but it is hard, and there are layers of complexity. It is almost a case that if you don't actually understand, then you think you understand - but your understanding is wrong.

As you know, the syntax is =CALCULATE(<expression>,filter1, filter2, filterN)

What they are saying is that IF calculate creates context transition, then ALSO has one or more filters, then the filters will overwrite any filter from context transition. This is correct. But the point is that the tables themselves (in this case FILTER tables) are evaluated first in the current filter context before context transition. I talk about this further down the page.

I understand this in a little bit other words, may be wrong:

1. Context transition affects only the "expression" part of CALCULATE.
2. Filter part of CALCULATE (raw filter or FILTER or other table filter) operates in outer (towards CALCULATE) filter context. It means that if we use raw filter, we actually rewrite filter context to explicitly named columns (because of hidden FILTER(ALL(column),...). If we use FILTER, then "table" argument of FILTER goes from outer filter context, then it could be modified by ALL etc.
3. So if there is outer CALCULATE, it performs context transition to nested CALCULATE, and then filter part of latter get filtered table/column for iteration. Because all nested CALCULATE (both expression and filters part) is an "expression" part of outer CALCULATE
4. As there are no outer filters in calculated column, then FILTER gets all table provided as its first argument.

I don't really understand perfectly what you are saying, but I think you are correct. Let me describe it differently and see if it is consistent with your understanding.

A CALCULATE formula ALWAYS executes in the following order
1. Execute every filter parameter, one at a time (if any exist). This includes the conversion of a simple filter to a table filter (as you have said). Note the CALCULATE doesn't actually do this calculation at all - the Power Pivot engine creates the tables and passes them back to CALCULATE for execution. More on that later
2. Then execute context transition (but only if there is a row context of course)
3. Then apply the filters from step 1 over writing the filters from context transition if necessary.

Back to step 1: CALCULATE delegates each FILTER table function to the Power Pivot engine for execution. CALCULATE is then the "receiver" of the filtered tables passed back to it by the Power Pivot engine. So by the time CALCULATE gets to use these tables, they have already been resolved - well before CALCULATE does its job of changing the filter context. The all table functions inside a CALCULATE are executed in the current filter context - whatever that is. The CALCULATE statement has no impact on the filter context for the FILTER table functions inside the CALCULATE itself.

So then there are nested CALCULATE statements. The syntax is

=CALCULATE( CALCULATE(<expression>,filter1, filter2, filterN) )

The outer CALCULATE is executed first (because it is the first thing after the equal sign). This outer calculate doesn't have any filter parameters (in this case), so all it does is cause context transition (but only if there is a row context of course). Then the inner CALCULATE is executed next. CALCULATE always executes (delegates for execution actually) the filter parameters before the expression part so it goes ahead and resolves each of the filter table functions (in the current filter context). This time context transition has already occurred by the outer CALCULATE (if there is an outer row context) and hence you get a different result.

Wait, what context transition?

the one that comes in step 2 (not step 1). the ALLEXCEPT is executed in the current filter context. The context transition comes next (step2) before the tables are applied as filters (step 3)

ALLEXCEPT is a filter part of a CALCULATE, so why is transition happens there?

It doesn't. it happens in step 2. First the ALLEXCEPT is delegated to the power pivot engine by CALCULATE and it is executed in the current filter context. THEN context transition occurs, THEN ALLEXCEPT removes the filters (step 3).

so, how much rows comes here to the FILTER as a table argument? In other words, what it the table under ALL(pc[engine])? A one-column table of the distinct values in pc[engine] column?

Yes

If yes, then FILTER iterates over all these values, select only rows with [engine]="a", then, as you described below, CALCULATE got a one-row-one-column table for filter propagation on 'pc' table. And in 'pc' table (filtered this way) then ALL rows with [engine]="a" will remain?

Yes

If yes, there have to be rows with any [site] corresponding with [engine]="a". In this case COUNTROWS(pc) will calculate the same amount of rows for each row in 'report' table.

No.

This following statement I made is actually incorrect:

"Here comes the key point: => There is no context transition in any of these formulas (2, 3, or 3.1 )."

I knew what I wanted to say, but I said it wrong and that caused confusion. (I will change it back in the previous post too to prevent further confusion). What I should have said was this:

"Here comes the key point: => No context transition is affecting any of the internal table filters in any of these formulas (2, 3, or 3.1 ). There is context transition, just that it comes AFTER the table filter formulas are already resolved"

With this now correct knowledge, the filters from context transition get applied first, then the filter from the table functions. All rows that are not pc[engine] = "a" are therefore blank.

Error 2. The second error I made was when I said this is filter propagation masquerading as context transition - this was wrong. Actually both are occurring. Following my 3 step process, first the tables functions are resolved in the current filter context, then context transition occurs, then finally the filters are applied - filter propagation).

Wait-wait-wait. This is totally unclear:
If standalone ALL(pc[site]) removes entire 'pc' table from filter context, then there should be all rows from 'pc' table for COUNTROWS. But they still filtered by row context!

ALL(pc[site]) does not remove entire PC table from the filter context. It removes the column PC[Site] from the filter context. I agree this is strange and i don't fully understand. I will be seeing Marco next week so I will try to ask him if I get a chance.

I have been working on this reply on and off for the last 14 hours (with a training course in between). I think i best release what I have and then let you come back with more questions. Hopefully I have improved the clarity by removing a few errors and some better explanation.

[hohlick]

Hi Matt!

Hopefully I have improved the clarity by removing a few errors and some better explanation.

Thanks a lot, now it MUCH more clear! I thought it is not so hard question, it described so much times in books and posts. But when I tried to explain context transition for my friend, I got the same questions that confused me also. So after a big discussion, where not all becomes clear, I came to guru

I agree this is strange and i don't fully understand. I will be seeing Marco next week so I will try to ask him if I get a chance.

Hope Marco could clear this issue for us. The easiest explanation is that filter on linked column in dependent table cannot be cleared... then they had to tell us about it!

Cheers

[Ilya_B]

Hello, colleagues, thank you for interesting discussion!
But there are many monents, that very unclear to me yet. Sorry for bad English, I haven't English practice for a long time, but hope that you can understand me

Matt, when you said that the second CALCULATE argument overwrite the context transition, you mean that it "delete" the filter context (transition context) and creates new filter context by the second argument or you mean, that second argument can complement the filter comtext, and if they are use the same columns, the second argument can overwtite it?

In other words: if i have the filter context by context transition (column 1 = 'a'; column 2= 'b') and I have the table, as result a work by second argument in CALCULATE ( column 2 = 'e'; column 2='f').
The final filter context for the first argument in CALCULATE will be ( column 1 = 'a'; column 2 = 'e'; column 2='f') or (column 2 = 'e'; column 2='f')?

[hohlick]

I think I got the clue for solution of this issue, when we try to change or clear filter context on linked column in related table only, and do not
I do not sure whether my explanation is correct in terms of Query Plans or how VertiPaq works in reality, but is seems to work in calculated columns. I mean, it could be technically wrong explanation, but for now, I cannot find when my explanation do not explain

I want to test it on more complex models and have some reading too before place an answer here, but it'll take not much time, I think.

PS: Do you know how to intrigue someone? I'll tell you. Later.

[MattAllington]

As you have pointed out, it is confusing to us all. We are all learning, and discussion like this really helps. I will be turning this into a blog post when I get some time.

To answer your question, it is the latter. Following my 3 step process, step 2 is context transition. Step 3 is applying the filters. If the filters act on the same column(s), they overwrite the context transition and replace it with the new filter context. If the filters are on different columns. They are added to the filter context.

To learn more enroll to my Power BI Online Training.

[matthew]

Great discussion. Never can have too much discussion on DAX.

They explain it correctly but it is hard, and there are layers of complexity. It is almost a case that if you don't actually understand, then you think you understand - but your understanding is wrong.

Seriously. This is me. On a lot of the nuances of DAX i'll be "I understand this now" - but later will run into a situation where it turns out "Er, no I don't".

Here is a link to a great article which explains context transition that everyone should read:
http://mdxdax.blogspot.com/2011/03/logic-behind-magic-of-dax-cross-table.html