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

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Anonymous,

Please check the following blog to know more about variable function usage in Dax calculations:

Using Variables in DAX 

Regards,

Xiaoxin Sheng

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous ,

     

    Appriciate your responce.

    If variable doesn’t work, is there anything help me out to make my DAX expression efficient?

     

    Thanks,

    Saad

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      HI Anonymous ,

      Can you please share some dummy data for test? It is hard to modify your formula without any sample data.

      In addition, you can also try to use the following formula if it suitable for your requirement:

      Total_workforce_FLC EURO(M/Y-1) =
CALCULATE (
    CALCULATE (
        SUM ( MOR_Data[Current month Fully Loaded Costs Euro] )
            + SUM ( MOR_Data[Current month Fully Loaded Costs Euro] ),
        MOR_Data[STATUS] <> "Contingent",
        SAMEPERIODLASTYEAR ( 'Calendar'[Date] )
    ),
    FILTER (
        ALLSELECTED ( MOR_Data ),
        SWITCH (
            SELECTEDVALUE ( 'Axis_Table'[Type] ),
            "Year", TREATAS ( VALUES ( 'Axis_Table'[Values] ), 'Calendar'[Year_Cal] ),
            "Quarter", TREATAS ( VALUES ( 'Axis_Table'[Values] ), 'Calendar'[Quater-Year] ),
            "Month", TREATAS ( VALUES ( 'Axis_Table'[Values] ), 'Calendar'[MonthYear_Cal] ),
            FALSE ()
        )
    )
)

      Regards,

      Xiaoxin Sheng