topic Measure with multiple conditions in DAX Commands and Tips

Measure with multiple conditions

alexvanlauwe — Sat, 24 Oct 2020 18:58:43 GMT

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.

Re: Measure with multiple conditions

MattAllington — Sat, 24 Oct 2020 19:45:17 GMT

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.

Re: Measure with multiple conditions

mahoneypat — Sun, 25 Oct 2020 13:07:18 GMT

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

Re: Measure with multiple conditions

alexvanlauwe — Mon, 26 Oct 2020 09:11:07 GMT

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

Re: Measure with multiple conditions

alexvanlauwe — Thu, 29 Oct 2020 11:07:18 GMT

Hi Pat,

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

Thank you again for your help !