MattAllington wrote: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.
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')
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:
- Context transiton affects only the "expression" part of CALCULATE.
- 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.
- 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
- 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:
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!