var different result

[Steve_L]

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
)

[v-jiascu-msft]

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

[erik_tarnvik · Accepted 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.