topic Re: Context Transition Different with Measure Reference Even with CALCULATE in DAX Commands and Tips

Context Transition Different with Measure Reference Even with CALCULATE

Anonymous — Tue, 19 Oct 2021 04:29:51 GMT

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.

Re: Context Transition Different with Measure Reference Even with CALCULATE

VahidDM — Tue, 19 Oct 2021 05:22:56 GMT

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!!

Re: Context Transition Different with Measure Reference Even with CALCULATE

Anonymous — Tue, 19 Oct 2021 13:39:09 GMT

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?

Re: Context Transition Different with Measure Reference Even with CALCULATE

VahidDM — Tue, 19 Oct 2021 20:36:45 GMT

Anonymous

Please see this link:

https://radacad.com/caution-when-using-variables-in-dax-and-power-bi

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

Appreciate your Kudos!!

Re: Context Transition Different with Measure Reference Even with CALCULATE

Anonymous — Mon, 25 Oct 2021 13:18:55 GMT

VahidDM, thank you for referring me to this.

Unfortunately, while it's a helpful article, it does not address my original question. I understand that variables are evaluated in their original context.

The question, in essence, is why the two versions of Measure_2 above would yield different results. They both involve CALCULATE, so the same context transition should be ocurring.