VAR RETURN question

[Pikachu-Power]

Hi all,

i have the following two measures:

MyMeasure1 = IF(SUM(Table[Amount])<1000,0,SUM(Table[Amount])-1000) 

and with a correction of the total:

MyMeasure2 = IF(HASONEFILTER(Table[Year]),
IF(SUM(Table[Amount])<1000,0,SUM(Table[Amount])-1000),
SUMX(FILTER(Table,[Amount]>1000),[Amount]-1000)
)

 The results looks fine.

But when i wrap it in a VAR RETURN i get a double higher value for MyMeasure2:

VAR MyMeasure2 = ...

VAR MyMeasure3 = ...

VAR MyMeasure4 = ...

RETURN

MyMeasure2

Do I have to write it always in a separet measure or it is possible to include MyMeasure2 in VAR RETURN?

[Pikachu-Power]

Hello chenwuz,

yes, thats exactly what I want. But get than wrong result in writing it in one measure. As far as I understand it dont work in this way right?

[amitchandak]

@Pikachu-Power , var is executed when it appears, if you use a filter on top of var, it might not work

refer

https://towardsdatascience.com/three-things-you-need-to-know-when-using-variables-in-dax-c67724862b5...

https://radacad.com/caution-when-using-variables-in-dax-and-power-bi

[Pikachu-Power]

When I write a separate Measure...

MyMeasure1 = IF(SUM(Table[Amount])<1000,0,SUM(Table[Amount])-1000) 

and include it in a second Measure...

VAR MyMeasure2 = 
IF(HASONEFILTER(Table[Year]),
MyMeasure1,
SUMX(FILTER(Table,[Amount]>1000),[Amount]-1000)
)

RETURN
VAR MyMeasure2

I get the right Value. But couldnt really comprehend this behaviour and if it is possible to write it in one Measure.

[v-chenwuz-msft]

Hi @Pikachu-Power ,

Is that you want this?

Measure =
VAR MyMeasure1 = IF(SUM(Table[Amount])<1000,0,SUM(Table[Amount])-1000) 
VAR MyMeasure2 =
    IF (
        HASONEFILTER ( Table[Year] ),
        MyMeasure1,
        SUMX ( FILTER ( Table, [Amount] > 1000 ), [Amount] - 1000 )
    )
RETURN
    MyMeasure2

About var:

Stores the result of an expression as a named variable, which can then be passed as an argument to other measure expressions. Once resultant values have been calculated for a variable expression, those values do not change, even if the variable is referenced in another expression.

VAR keyword (DAX) - DAX | Microsoft Docs

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.