Forum Discussion
Context transition issues
Anonymous, v-juanli-msft
For those interested, it appears this is actually a bug.
See this other post.
Hi AlB
1.
When you use calculate, it do a context transition, converts the content to current row, so the test1 and test2 shows no error, it returns the current row for the column.
When you delete the calculate, it throws an error for test1 and test2.
2.
an example description of the evaluation order of the filter arguments and the context transition in CALCULATE
This is the exact evaluation order:
1.Evaluating filter arguments in the current filter context
2.Performing the context transition
3.Applying the filter arguments evaluated in step 1
3. formuls you can use to replace Test2,
test5 = COUNTROWS(ALL(Product[ProductKey]))
or
test6 = CALCULATE(COUNTROWS(VALUES('Product'[ProductKey])),ALL('Product'))
Best Regards
Maggie
Hi Maggie. Many thanks for your reply.
I am not really looking for substitutes for the expressions but just trying to understand why Test2 exhibits a behaviour different from what I expect. Test1, Test3 and Test4 all work as I expect. Let's recap. We have this calculated column:
‘Product’[Test 2]=CALCULATE(ALL(’Product’[ProductKey]))
My reasoning here is, and I am following the steps you describe (my comments in green):
1.Evaluating filter arguments in the current filter context. Ok. It's a calculated column so no current filter context here
2.Performing the context transition. Right. So at this point the current row context is translated into a filter context. The filter contains the current row values.
3.Applying the filter arguments evaluated in step 1. No filter context from step 1 but I guess you would apply the filter resuting from context transition in step 2. So we apply that, which results in the current row of 'Product' but then the ALL eliminates that for the ProductKey column. We should then get the whole 'Product'[ProductKey] column but we get only its value on the current row, judging by the result.
Although unlikely, let's assume for a moment that the ALL is applied to 'Product'[ProductKey] before the filter from context transition is applied. That would justify the result in Test2. If that is the way it works, I would expect
‘Product’[Test 3]=CALCULATE(COUNTROWS(ALL(’Product’[ProductKey])))
to yield 1 for each row BUT it yields 2517 (the number of rows of the full 'Product'[ProductKey] column). So in
‘Product’[Test 2]=CALCULATE(ALL(’Product’[ProductKey])) it looks like the system is taking only the current row of 'Product'[ProductKey] but in
‘Product’[Test 3]=CALCULATE(COUNTROWS(ALL(’Product’[ProductKey]))) it looks like the system is working with all rows of 'Product'[ProductKey]. That's what I don't get. The addition of COUNTROWS seems to affect the way ALL() works??
Thanks a lot
Here's what I think is going on with your Test 2:
Product[Test 2]=CALCULATE(ALL(’Product’[ProductKey]))
CALCULATE only requires one input, ALL (Product [ProductKey]) in your example. So what happens if we create a table just off of that:
ALL and VALUES will return the same table in this example. Back to the formula :Test 2 (CALC just ALL) = CALCULATE(ALL( 'Just product Keys'[ProductKey]))
We now know that the ALL returns a table of all the product keys. So what does it do with them?! CALCULATE takes the row you are in (have row context since you are in a calculated column) and transitions that from a row context to a filter context. In the first row of the example, we have Product Key =1 and then that is compared to the ALL product key, which is then why you just get the product key in this calculated column. Long and short of it is that where the ALL is used in this calculated column does not remove any filters ( since it is not used as filter input), just returns a column without any filters, and then the current row is used as a filter.
So what happens in your test 3??
Test 3 (CALC, COUNTROWS, ALL) =
CALCULATE(
COUNTROWS(
ALL('Just product Keys'[ProductKey])
)
)Now ALL is being used differently. ALL really has two functions: Ignore Filters and Return a table. In this situation it is being used to ignore the filer context ( the row product key in our example) and return the entire table to be be counted. That is why it works without CALCULATE, beacuse its not doing anything.
Then what about test 4?
Test 4 = CALCULATE( COUNTROWS( VALUES('Just product Keys'[ProductKey]) ) )Here Values is only able to return a table, it has nothing to do with ingoring filters like ALL. So we end up with 1 for each row since here calculated transforms the current row context into a filter context which is then applied to the Values. Since these are Keys, no dupes, the answer is one for each row. If you remove the CALCULATE from this one you end up with the entire table since no context transition takes place.
And just for fun (well I guess fun, not so sure at this point..) what if we mix FILTER, ALL, COUNTROWS?
Column = COUNTROWS( FILTER ( ALL( DimProduct[ProductKey]), DimProduct[ProductKey]<= 10 ) )The general way CALCULATE is used is :
=CALCULATE( [Measure], Some sort of filter)
Filters could be simple such as Product[Color] = "Red" or more complex using FILTER:
FILTER( ALL ( Product[Color]))This can be a pretty complex topic, and I admit I struggled to say why this was happeninng, I understood it just hard to put into words. I think it will just take some time in developing in DAX and seeing the theory play out in reality. But also need to keep up with the theory. Hope this helps and not made it more confusing:smileyfrustrated:
Anonymous
Hi Nick. Once more, thanks for a fantastic answer. You've explained it quite well.
Now what I don't quite get, and is starting to piss me off, is when exactly ALL() works as REMOVEFILTERS (as the Italians call it) and when as returning all rows. In fact I was reading recently about this in the Definitive Guide to DAX and they state this (the red highlighting is mine):
It is worth it to note that ALL behaves as REMOVEFILTERS only when you use it as a top-level parameter in a filter argument of CALCULATE. When you use it as a regular table function, it does exactly what it is supposed to do: It returns a table.
and provide two illustrative examples. This as top-level parameter in a filter argument (and thus REMOVEFILTERS):
CALCULATE (
COUNTROWS ( 'Date' ),
ALL ( Sales )
)and this as regular table function (returns a table):
CALCULATE (
COUNTROWS ( 'Date' ),
FILTER(ALL ( Sales ), TRUE())
)Unfortunately, the ALL() in our Test2 and Test3 do not seem to fall exactly in any of those two categories so I am struggling :smileyfrustrated:. I guess the ALL() in Test2 and Test3 both look more like the second case, since neither is a top-level parameter filter argument of CALCULATE. But then none of them should be working as REMOVEFILTERS and one is (according to your explanation and the results, that back it up). I cannot really tell the difference. Maybe the definition in the book is not extensive enough?
Thanks so much