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 doesn’t work if i use variable, but second work perfectly if i don't use variables. (The Dark Grey Bar's are the one causing issue)
Can anyone please help what i am doing wrong in first scenario.
merry christmas

 

scenario 1 (Incorrect) :-

 

Total_workforce_FLC EURO(M/Y-1) =
Var FirstSUm = CALCULATE(SUM(MOR_Data[Current month Fully Loaded Costs Euro]),MOR_Data[STATUS] <> "Contingent",SAMEPERIODLASTYEAR('Calendar'[Date]))
Var SecondSum = CALCULATE(SUM(MOR_Data[Current month Fully Loaded Costs Euro]),MOR_Data[STATUS] = "Contingent",SAMEPERIODLASTYEAR('Calendar'[Date]))
return
if(HASONEVALUE('Axis_Table'[Type]),
SWITCH(VALUES(Axis_Table[Type])
,"Year",CALCULATE(FirstSUm +
SecondSum
,TREATAS(VALUES('Axis_Table'[Values])
,'Calendar'[Year_Cal]))
,"Month",CALCULATE(FirstSUm +
SecondSum
,TREATAs(VALUES('Axis_Table'[Values])
,'Calendar'[MonthYear_Cal]))
,"Quarter",CALCULATE(FirstSUm +
SecondSum
,TREATAs(VALUES('Axis_Table'[Values])
,'Calendar'[Quater-Year]))
)
)
 
 
scenario 2(Correct) :-

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

 

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

12 Replies

  • mwegener's avatar
    mwegener
    Most 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
        Most 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

    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