topic Re: Problem using variables in DAX Commands and Tips

Problem using variables

Anonymous — Tue, 27 Aug 2019 11:16:16 GMT

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.

Re: Problem using variables

AlB — Tue, 27 Aug 2019 12:19:59 GMT

Hi Anonymous

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.

Re: Problem using variables

Anonymous — Tue, 27 Aug 2019 15:23:00 GMT

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.

Re: Problem using variables

AlB — Tue, 27 Aug 2019 17:17:06 GMT

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