Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Problem using variables

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.

 

1 ACCEPTED SOLUTION
AlB
Community Champion
Community Champion

 

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

View solution in original post

3 REPLIES 3
AlB
Community Champion
Community Champion

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.     

Anonymous
Not applicable

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
Community Champion
Community Champion

 

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.