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]),
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?

Need Help

4 Replies

    • 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.

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

    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?