Dax error using switch as nested comaprison

Question

Hi I'm trying to get a final column based on two measures values, I'm using Power BI but i'm new on this. here's the formula. i'm getting an error" function switch dos not support comparing values of type true/false with values of type text"

SWITCH(
    AND(('Table'[ar]*100)>=-100,('Table'[ar]*100)<=-5),
        SWITCH(
          AND(('Table'[br]*100)>=-5,('Table'[br]*100)<=5),"DROP TO AVG",
          AND(('Table'[br]*100)<=100,('Table'[br]*100)>=5),"DROP TO HIGH",
          AND(('Table'[br]*100)>=-100,('Table'[br]*100)<=-5),"BAD ALERT"),
    AND(('Table'[ar]*100)>=-5,('Table'[ar]*100)<=5),
        SWITCH(
          AND(('Table'[br]*100)<=100,('Table'[br]*100)>=5),"KEEP HIGH",
          AND(('Table'[br]*100)>=-100 ,('Table'[br]*100)<=-5),"KEEP LOW"),
    AND(('Table'[ar]*100)<=100,('Table'[ar]*100)>=5),
        SWITCH(
          AND(('Table'[br]*100)>=-5,('Table'[br]*100)<=5),"INCREASE TO AVG",
          AND(('Table'[br]*100)>=-100,('Table'[br]*100)<=-5),"INCREASE TO LOW",
          AND(('Table'[br]*100)<=100,('Table'[br]*100)>=5),"GOOD ALERT"))

Alexis Olson Alexis Olson · Accepted Answer · 2018-12-11T18:05:40

You're getting that error since you've got some misplaced parentheses that include text within an AND function. E.g. AND(('Table'[br]*100)>=-100,('Table'[br]*100)<=-5),"BAD ALERT"),. Text can't logically be combined with True/False.

I think you're looking for something more like this:

SWITCH(TRUE(),
    ('Table'[ar]*100 >= -100) && ('Table'[ar]*100 <= -5),
    SWITCH(TRUE(),
        ('Table'[br]*100 >=  -5 ) && ('Table'[br]*100 <=  5), "DROP TO AVG",
        ('Table'[br]*100 <=  100) && ('Table'[br]*100 >=  5), "DROP TO HIGH",
        ('Table'[br]*100 >= -100) && ('Table'[br]*100 <= -5), "BAD ALERT"
    ),
    ('Table'[ar]*100 >= -5) && ('Table'[ar]*100 <=5),
    SWITCH(TRUE(),
        ('Table'[br]*100 <=  100) && ('Table'[br]*100 >=  5), "KEEP HIGH",
        ('Table'[br]*100 >= -100) && ('Table'[br]*100 <= -5), "KEEP LOW"
    ),
    ('Table'[ar]*100 <= 100) && ('Table'[ar]*100 >= 5),
    SWITCH(TRUE(),
        ('Table'[br]*100 >=  -5 ) && ('Table'[br]*100 <=  5), "INCREASE TO AVG",
        ('Table'[br]*100 >= -100) && ('Table'[br]*100 <= -5), "INCREASE TO LOW",
        ('Table'[br]*100 <=  100) && ('Table'[br]*100 >=  5), "GOOD ALERT"
    )
)

Dax error using switch as nested comaprison

1 Answers