Context transition issues

[AlB]

Hi all,

I have the following in a calculated column of the ‘Product’ table from the Contoso DB:

A) ‘Product’[Test 1]=CALCULATE(VALUES(’Product’[ProductKey])) yields the ProductKey per row, as expected

B) ‘Product’[Test 2]=CALCULATE(ALL(’Product’[ProductKey])) ALSO yields the ProductKey per row, NOT AS EXPECTED.

C) ‘Product’[Test 3]=CALCULATE(COUNTROWS(ALL(’Product’[ProductKey]))) however yields 2517 in all rows (size of the Product table) as expected.

D) ‘Product’[Test 4]=CALCULATE(COUNTROWS(VALUES((’Product’[ProductKey])))) yields a 1 in all rows as expected.

My question is, what is going on in ‘Product’[Test 2]? Since we are using  ALL(), I would expect the operation to yield an error for returning a table rather than a scalar. If the filter resulting from the context transition is overridden by the ALL(), we would have multiple rows as result of the CALCULATE. Why is the behavior here different from what we see in C) and D), where the use of ALL does result in a different outcome?

Thank you very much

[v-juanli-msft]

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

[AlB]

@v-juanli-msft

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

[Anonymous]

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

[AlB]

@Anonymous

Ok, so you were looking for some additional "fun" with your Column but the fun is not complete without  our friend CALCULATE. So I've added a CALCULATE and created a further variant with VALUES instead of ALL:

 

Col_B yields the same result as your Column, i.e. 10 in all rows. In this case, ALL is just returning a (one-column) table with all rows of 'Product'[ProductKey] and thus the result is the same for each row, regardless of context transition. 

Col_C is also interesting. Since we use VALUES and not ALL this time, the table used by FILTER is in this case affected by the filter resulting from context transition. Since inside FILTER we have two nested row contexts for 'Product'[ProductKey], let's agree that we'll refer to the table inside FILTER as TableFil and we'll use 'Product'[ProductKey] to refer to the column of the overall table (which would be EARLIER('Product'[ProductKey]) within FILTER). TableFil is a one-row column with value that of  the current row in 'Product'[ProductKey]. FILTER scans TableFil and checks each row (only one thus) against the condition. The return value of FILTER will be a one-row column at rows with 'Product'[ProductKey]<=10 and an empty table for the rest. Thus, with COUNTROWS, the result per row of 'Product'[Col_C] will be a 1 for the former group and a blank for the latter.

Agreed? In this case all seems to add up nicely             

[AlB · Accepted solution]

@Anonymous, @v-juanli-msft

For those interested, it appears this is actually a bug. 

See this other post.

[Anonymous]

Good catch! Do you get a prize from msft? 

[AlB]

@Anonymous

I would support the idea    but it wouldn't be fair. Other people saw it first, as can be read on the other post.

[AlB]

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