Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Context Transition Different with Measure Reference Even with CALCULATE

Hi, I'm struggling to grasp why I'm experiencing the following.

 

When I use a reference to Measure_1 (which begins with CALCULATE) in Measure_2, I get one result. When I simply copy the text from Measure_1 into Measure_2, I get another result.

 

I understand that I should expect this if Measure_1 didn't begin with CALCULATE because of the context-transition ocurring with measure-references. However, since Measure_1 begins with CALCULATE, shouldn't the context-transition be the same regardless of if I reference Measure_1 or use its code? Why am I getting different results?

 

 

Measure_1 := 
CALCULATE (
    VALUES ( Date_Table[Week_ID] ),
    FILTER (
        Date_Table,
        Date_Table[Date] = MAX ( Sales_Table[Order_Date] )
    )
)

// Date_Table is marked as the date-table.

// Date_Table[Date]   1 -------------- *   Sales_Table[Order_Date]

 

 

 

Measure_2 :=
CALCULATE (
    SUM ( Sales_Table[Sales_Amount] ),
    FILTER ( Date_Table, Date_Table[Week_ID] = [Measure_1] )
)

/*

The above returns a different result than just copying the code from Measure_1 into Measure_2:

Measure_2 :=
CALCULATE (
    SUM ( Sales_Table[Sales_Amount] ),
    FILTER (
        Date_Table,
        Date_Table[Week_ID]
            = CALCULATE (
                VALUES ( Date_Table[Week_ID] ),
                FILTER ( Date_Table, Date_Table[Date] = MAX ( 'Sales_Table'[Order_Date] ) )
            )
    )
)

*/

 

 

Again, I was expecting the results to be the same since CALCULATE begins Measure_1--i.e., context transition ocurring due to the implicit CALCULATE in the measure-reference or explicit CALCULATE in the pasted code.

 

Any guidance would be much appreciated.

 

A specific reference to where I can find an explanation in the Definitive Guide to Dax (or elsewhere online) would be appreciated as well if possible.

 

Many thanks.

4 Replies

  • Hi Anonymous 

     

    Change VALUES in the Measure 1 to MAX and try that.

     

    If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

    Appreciate your Kudos!!

     

    • Anonymous's avatar
      Anonymous
      Not applicable

      Hi VahidDM , thanks for your quick reply.

       

      Week_ID is a text-column ("week 1", "week 2" etc.).

       

      I'm more interested in learning why the result is different when CALCULATE is present in both methods; I was expecting the context-transition to be the same.

       

      Do you know why the results are different?