Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi
I have a problem of using VAR in DAX recently that I try to use VAR to make my formula more readable, but finally it returns totally different result, is there anyone can help me with this? Thanks a lot!!
Original Code, this code works well for me that ranks the products according to the quantity sold in current month:
= RANKX ( ALL ( 'Sales'[Product Name] ), CALCULATE ( SUM ( 'Sales'[Value] ), 'Sales'[Value Type] = "QTY", LASTDATE ( 'Sales'[Month/Year] ) ), , DESC, DENSE )
Modified Code, this code reutrns 1 for all of my products:
=VAR QTY1M = CALCULATE ( SUM ( 'Sales'[Value] ), 'Sales'[Value Type] = "QTY", LASTDATE ( 'Sales'[Month/Year] )) return RANKX ( ALL ( 'Sales'[Product Name] ), QTY1M, , DESC, DENSE )
Solved! Go to Solution.
In the first example CALCULATE is evaluated within the iteration context of the RANKX function. In the second example, the CALCULATE function is evaluated first (within whatever context you place the whole expression) and the result QTY1M is used within the RANKX function.
Hi @Steve_L,
You can create a new measure instead of a var formula. It looks like this:
QTY1M = CALCULATE ( SUM ( 'Sales'[Value] ), 'Sales'[Value Type] = "QTY", LASTDATE ( 'Sales'[Month/Year] ))
FinalMeasure =
RANKX ( ALL ( 'Sales'[Product Name] ), [QTY1M], , DESC, DENSE )
Best Regards!
Dale
In the first example CALCULATE is evaluated within the iteration context of the RANKX function. In the second example, the CALCULATE function is evaluated first (within whatever context you place the whole expression) and the result QTY1M is used within the RANKX function.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
96 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |