Forum Discussion

Pikachu-Power's avatar
Pikachu-Power
Icon for Impactful Individual rankImpactful Individual
4 years ago

VAR RETURN question

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...
Need Help
Pikachu-Power's avatar
Pikachu-Power
Icon for Impactful Individual rankImpactful Individual
4 years ago

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's avatar
v-chenwuz-msft
Icon for Community Support rankCommunity Support
4 years ago

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.