CODEWITHSUNDEEP
powerbidax
Betsy Curbelo
Betsy Curbelo

Reputation: 579

Dax error using switch as nested comaprison

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"))

Upvotes: 0

Views: 382

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40244

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"
    )
)

Upvotes: 1

Related Questions