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 fi...
  • 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

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