Forum Discussion

webportal's avatar
webportal
Icon for Impactful Individual rankImpactful Individual
8 years ago
Solved

Use DAX variable withing measure

Hello,

 

Is it possible to use a variable within a DAX measure expression?

For example, the following measure isn't working (it always returns 0):

 

Measure = 
VAR ThisMonth =
    CALCULATE (
        ABS ( SUM ( 'Table'[Saldo] ) );
        FILTER ( Table; Table[Conta] = 71 )
    )
VAR PreviouzMonth =
    CALCULATE (
        ThisMonth;
        PREVIOUSMONTH ( 'Calendário'[Date] );
        FILTER ( ALL ( 'Calendário'[Mês] ); MAX ( 'Calendário'[Mês] ) > 1 )
    )
RETURN
    ThisMonth-PreviouzMonth

But if the two variables above are calculated separetely - ie as two different measures - the calculation works fine.

 

Thanks for supporting!

Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    Hi webportal,

     

    No. We cannot use nested variables in such a scenario. When the second variable referrs to the first variable, the first one always pass the static value to it. In your scenario, if variable ThisMonth returns 100, then, second variable will receive 100 as its result, regardless of its own calculation. That is why the measure always return 0.

     

    To resolve this problem, rather than using the first variable inside second one, please input the actual calculation.

    Measure = 
VAR ThisMonth =
    CALCULATE (
        ABS ( SUM ( 'Table'[Saldo] ) );
        FILTER ( Table; Table[Conta] = 71 )
    )
VAR PreviouzMonth =
    CALCULATE (
        CALCULATE (
        ABS ( SUM ( 'Table'[Saldo] ) );
        FILTER ( Table; Table[Conta] = 71 )
    );
        PREVIOUSMONTH ( 'Calendário'[Date] );
        FILTER ( ALL ( 'Calendário'[Mês] ); MAX ( 'Calendário'[Mês] ) > 1 )
    )
RETURN
    ThisMonth-PreviouzMonth

    Best regards,
    Yuliana Gu

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    8 years ago

    Yes, that should be possible. However, it is likely taking into account the context of the measure and so something is going wonky versus with separate measures it sounds like they are getting calculated in a different context.

    • webportal's avatar
      webportal
      Icon for Impactful Individual rankImpactful Individual
      8 years ago

      Yes, I guess it has to do with the context of the calculation.
      Notice however that the second variable is using the first variable in its expression, is that ok?

      • v-yulgu-msft's avatar
        v-yulgu-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        8 years ago

        Hi webportal,

         

        No. We cannot use nested variables in such a scenario. When the second variable referrs to the first variable, the first one always pass the static value to it. In your scenario, if variable ThisMonth returns 100, then, second variable will receive 100 as its result, regardless of its own calculation. That is why the measure always return 0.

         

        To resolve this problem, rather than using the first variable inside second one, please input the actual calculation.

        Measure = 
VAR ThisMonth =
    CALCULATE (
        ABS ( SUM ( 'Table'[Saldo] ) );
        FILTER ( Table; Table[Conta] = 71 )
    )
VAR PreviouzMonth =
    CALCULATE (
        CALCULATE (
        ABS ( SUM ( 'Table'[Saldo] ) );
        FILTER ( Table; Table[Conta] = 71 )
    );
        PREVIOUSMONTH ( 'Calendário'[Date] );
        FILTER ( ALL ( 'Calendário'[Mês] ); MAX ( 'Calendário'[Mês] ) > 1 )
    )
RETURN
    ThisMonth-PreviouzMonth

        Best regards,
        Yuliana Gu