Problem using variables

[cmalopez]

I need to calculate the sum of all amounts for a column in a table avoiding duplicate IDs in another column in same table.

At last I finished with two measures:

Max Order Gross Sales Amount =
       MAX(tBrand[ORDER_TOTAL_AMOUNT])

Brand_Gross_Sales_Amount =
        SUMX ( DISTINCT ( tBrand[ORDER_ID] ); [Max Order Gross Sales Amount] )

Using these two different measures all is fine, but when I tried to use variables in same measure like this:

Brand_Gross_Sales_Amount =
     VAR vMax_Order_Gross_Sales_Amount =
           MAX ( tBrand[ORDER_TOTAL_AMOUNT] )
RETURN
          SUMX ( DISTINCT ( tBrand[ORDER_ID] ); vMax_Order_Gross_Sales_Amount )

In thi case returned amount is wrong (it is a lot bigger than first one). I have checked and I cannot find the problem. May you help me?

Thanks a lot.

[AlB]

Hi @cmalopez 

Variables in DAX are immutable once they are assigned a value at declaration. In your example,

VAR vMax_Order_Gross_Sales_Amount = MAX ( tBrand[ORDER_TOTAL_AMOUNT] )

the MAX() is evaluated before the SUMX and then the value obtained stored in vMax_Order_Gross_Sales_Amount.

vMax_Order_Gross_Sales_Amount is used when the SUMX is iterating. Note that value is always the same.  THe measure, on the contrary, is calculated anew for each row as the SUMx iterates.     

[cmalopez]

Thank you so much for your response. So, is possible to calculate sum for column values dependig on distinct ID values in other column in a more easy way? Do I need to use two different measures?

Thank you so much again.

[AlB · Accepted solution]

I think the way you've set it up is best. You could have it all in one measure by simply using the code of the first one on the second one (note you need the CALCULATE to trigger context transition):

Brand_Gross_Sales_Amount =
SUMX (
    DISTINCT ( tBrand[ORDER_ID] );
    CALCULATE ( MAX ( tBrand[ORDER_TOTAL_AMOUNT] ) )
)

  but having two measures as you had is more versatile and the best practice.

Please mark as solved when we get to the solution so that others can see it too. If you find the posts useful please consider kudoing.

Cheers