Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Variable not evaluating inside switch statement, individual calculation is working fine.

Hello When i combine Variables with Switch statement it doesn’t work. But instead of variables I write entire calculation it work perfectly. Below are 2 scenario of my measure, The first scenario do...
Need Help
  • mwegener's avatar
    mwegener
    6 years ago

    Hi Anonymous ,

     

    check this out.

     

    Test Measure2 = 
CALCULATE (
    CALCULATE (
        SUM ( MOR_Data[Current month Fully Loaded Costs Euro] ),
        SAMEPERIODLASTYEAR ( 'Calendar'[Date] )
    ),
    FILTER (
        'Calendar',
        CONTAINS (
            VALUES ( Axis_Table[Values] ),
            Axis_Table[Values], SWITCH (
                SELECTEDVALUE ( Axis_Table[Type]),
                "Year", 'Calendar'[Year_Cal],
                "Quarter", 'Calendar'[Quater-Year],
                "Month", 'Calendar'[MonthYear_Cal]
            )
        )
    )
)

    Regards,

    Marcus

    Dortmund - Germany
    If I answered your question, please mark my post as solution, this will also help others.
    Please give Kudos for support.

  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Anonymous ,

    I'd like to suggest you use in operator to replace TREATAS function, it also works to compare unrelated fields and list of values:

    Total_workforce_FLC EURO(M/Y-1)2 = 
IF (
    HASONEVALUE ( 'Axis_Table'[Type] ),
    CALCULATE (
        CALCULATE (
            SUM ( MOR_Data[Current month Fully Loaded Costs Euro] ),
            SAMEPERIODLASTYEAR ( 'Calendar'[Date] )
        ),
        FILTER (
            'Calendar',
            SWITCH (
                SELECTEDVALUE ( Axis_Table[Type] ),
                "Year", 'Calendar'[Year_Cal] IN VALUES ( 'Axis_Table'[Values] ),
                "Month", 'Calendar'[MonthYear_Cal] IN VALUES ( 'Axis_Table'[Values] ),
                "Quarter", 'Calendar'[Quater-Year] IN VALUES ( 'Axis_Table'[Values] )
            )
        )
    )
)

    Regards,

    Xiaoxin Sheng

mwegener's avatar
mwegener
Icon for Most Valuable Professional rankMost Valuable Professional
6 years ago

Hi Anonymous ,

 

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.

https://docs.microsoft.com/en-us/dax/var-dax

 

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hello, mwegener ,

     

    Thanks for your reply. I got the understanding of how variable is evaluated.
    Is there any way i can optimize my DAX, without creating any external measure ?

    Thanks,
    Saad

    • mwegener's avatar
      mwegener
      Icon for Most Valuable Professional rankMost Valuable Professional
      6 years ago

      Hi Anonymous ,

       

      I played with your idea a bit. It was fun. 😄 

      You may download my PBIX file from here.
      Hope this helps.

       

      @ShanayaK

      You might also be interested in this.

       

      If I answered your question, please mark my post as solution, this will also help others.

      Please give Kudos for support.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hello mwegener 

         

        Thanks for sharing PBIX file, that is the same concept i am working on (Dynamic axis base on Month, Quarter and Year).
        I have used a different way to achieve it.
        Can you help me out in a measure i have created? Juts to simplify it or write it in an efficient manner without using any external measure. I have uploaded my PBIX, below is the link. (We Transfer file sharing)

         

        https://we.tl/t-xiSdDJhAV2

         

        I have created a "Test Measure" in which I have to repeat same calculation 3 times (Just for Year, Quarter and Month). Can minimize it to just once? using same concept and tables.
        Anonymous  appriciate your support as well 🙂

         

        Thanks