Forum Discussion

AlB's avatar
AlB
Community Champion
7 years ago
Solved

Context transition issues

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

Need Help

8 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    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's avatar
      AlB
      Community Champion
      7 years ago

      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's avatar
        Anonymous
        Not applicable
        7 years ago

        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: