Forum Discussion

alexvanlauwe's avatar
alexvanlauwe
New Member
5 years ago
Solved

Measure with multiple conditions

Hello to all,

 

So , I will try to explain my problem as precise as possible.

 

I would like to develop a measure with multiple conditions to determine the situation of the market :

 

I have first develop 2 others measures TM[TM3MR%VARPY] and TM[TM12MR%VARPY] that represent

  • the variation between total market 3 month rolling and total market 3 month rolling previous year
  • the variation between total market 12 month rolling and total market 12 month rolling previous year.

Then I would like to express a DAX function for multiple conditions to determine the situation the market :

If TM[TM3MR%VARPY]>0 && TM[TM12MR%VARPY]<0,"RECOVERY"

if TM[TM3MR%VARPY]>=0,02 && TM[TM12MR%VARPY]>=0,02,"EXPANSION"

if TM[TM3MR%VARPY]>TM[TM12MR%VARPY] && TM[TM3MR%VARPY]<0,02,"PEAK"

if TM[TM3MR%VARPY]<[TM12MR%VARPY],"MATURITY"

if [TM3MR%VARPY]<0 && [TM12MR%VARPY]>0,"CONTRACTION",
if [TM3MR%VARPY]<0 && [TM12MR%VARPY]<0,"TROUGH")
 

Then I try to use the "SWITCH" DAX function for multiple conditions but seems that it's something that don't master

see below

 

Measure = SWITCH TRUE(), TM[TM3MR%VARPY]>0 && TM[TM12MR%VARPY]<0,"RECOVERY",TM[TM3MR%VARPY]>=0,02 && TM[TM12MR%VARPY]>=0,02,"EXPANSION",TM[TM3MR%VARPY]>TM[TM12MR%VARPY] && TM[TM3MR%VARPY]<0,02,"PEAK",TM[TM3MR%VARPY]<[TM12MR%VARPY],"MATURITY",[TM3MR%VARPY]<0 && [TM12MR%VARPY]>0,"CONTRACTION",[TM3MR%VARPY]<0 && [TM12MR%VARPY]<0,"TROUGH")
 
Could you please help me with this?
 
Many thanks in advance for your help.

 

  • mahoneypat's avatar
    mahoneypat
    5 years ago

    You are missing "(" after SWITCH.  Also, I would first store your two measures as variables and then use the variables in the SWITCH evaluations.  The way it is written will cause a lot of recalculation.  Partial example below (note that you should not include the table name when referencing measures so other knows it is a measure and not a column reference).

     

    Measure =
    VAR varpy3M = [TM3MR%VARPY]
    VAR varpy12M = [TM12MR%VARPY]
    RETURN
        SWITCH (
            TRUE (),
            varpy3M > 0
                && varpy12M < 0, "RECOVERY",
            varpy3M >= 0.02
                && TM[TM12MR%VARPY] >= 0.02, "EXPANSION",
            "Other"
        )

     

    Regards,

    Pat

4 Replies

  • MattAllington's avatar
    MattAllington
    Icon for Community Champion rankCommunity Champion
    5 years ago

    It's hard to tell based on this information. The most likely issue is that you have to get the order of these correct. Switch will stop on the first result that returns true. If you can't work out what is happening I suggest you write each component as a simpnle measure and see what they return. 

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    5 years ago

    You are missing "(" after SWITCH.  Also, I would first store your two measures as variables and then use the variables in the SWITCH evaluations.  The way it is written will cause a lot of recalculation.  Partial example below (note that you should not include the table name when referencing measures so other knows it is a measure and not a column reference).

     

    Measure =
    VAR varpy3M = [TM3MR%VARPY]
    VAR varpy12M = [TM12MR%VARPY]
    RETURN
        SWITCH (
            TRUE (),
            varpy3M > 0
                && varpy12M < 0, "RECOVERY",
            varpy3M >= 0.02
                && TM[TM12MR%VARPY] >= 0.02, "EXPANSION",
            "Other"
        )

     

    Regards,

    Pat

    • alexvanlauwe's avatar
      alexvanlauwe
      New Member
      5 years ago

      Dear Pat,

       

      thank you for your answer.

       

      I tried your solution but I have another problem now. When I want to vizualize my measure , Power BI told me that

       
       
       

      SWITCH does not support comparing values of type TRUE/FLASE with values of type text.

      Consider using the value or format function to convert one of the values

       

      So I convert the text results with value function but I have still the same message.

       

      Please see below my measure:

       

      Measure =
      VAR varpy3M = [TM3MR%VARPY]
      VAR varpy12M = [TM12MR%VARPY]
      RETURN
      SWITCH(
      TRUE(),
      varpy3M >0 && varpy12M<0,VALUE("Recovery"),
      varpy3M >=0,02 && varpy12M>=0,02,VALUE("Expansion"),
      varpy3M > varpy12M && varpy3M<0,02,VALUE("Peak"),
      varpy3M < varpy12M,VALUE("Maturity"),
      varpy3M<0 && varpy12M>0,VALUE("Contraction"),
      varpy3M<0 && varpy12M<0,VALUE("Trough"))
       
      If you have any idea ? thank you again for your help
       
      Kind regards
    • alexvanlauwe's avatar
      alexvanlauwe
      New Member
      5 years ago

      Hi Pat,

       

      Finally it works , I found my mistake (Stupid mistake 😁)

       

      Thank you again for your help !