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 ,

 

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