Multiple If statements using columns

[samc_26]

Hi, I've been researching this all day and had no success so thought I would try here! I have a table which shows values and then three sets of targets to which I want to do some kind of if else statments against. I tried a conditional column but I think it's more complicated than this!  *Please excuse my attempt at writing DAX!

They are as follows:

if [value] < [Target], "Orange" else

if [value] => [Target] && < [High], "Green" else

if [value] > [High] && < [Purple], "Red" else

if [value] => [Purple], "Purple" else "0"

The ideal outcome is a new column that shows the colour that the value falls into so site 'A' would be 'Red', site 'C' would be 'Green' etc.

Any suggestions greatly appreciated! Thank you

[sevenhills · Accepted solution]

I believe you need is switch statement.

Check this link for more details: https://www.mssqltips.com/sqlservertip/7574/dax-case-statement-dax-if-switch/

and search for text "DAX SWITCH(TRUE())" and read from there.

I tried to understand and came with below syntax. Not sure whether it works correctly or not, give a try...

Your Column = 
SWITCH (
    TRUE (),
    Table1[Value] < Table1[Target], "Orange",
    Table1[Value] >= Table1[Target] && Table1[Value] < Table1[High], "Green",
    Table1[Value] >= Table1[High] && Table1[Value] < Table1[Purple], "Red",
    Table1[Value] >= Table1[Purple], "Purple",
    "0"
)

 

Output:

[AlexisOlson · Accepted solution]

A SWITCH is often neater than nested IFs. 

I think you could write it like this:

SWITCH (
    TRUE (),
    T[Value] < T[Target], "Orange",
    T[Value] < T[High], "Green",
    T[Value] < T[Purple], "Red",
    "Purple"
)

(Replace "T" with whatever your table name is.)

Edit: It looks like @sevenhills got there first. The only difference with mine is that I've eliminated the redundant conditions. For example, if you get past the "Orange" line, we already know that T[Value] >= T[Target], so we don't need to recheck it.

[samc_26]

You're both brilliant and have saved the day thank you so much! I've used this one as it looks more simple to write out! I'm self taught like most people so muddling my way through DAX and I saw some suggestions online for SWITCH but wasn't sure how to use it in this example. I was trying to use this column for some conditional formatting in a matrix and I've just managed to do it using this - I will be eternally grateful! Thank you! 🙌

[sevenhills · Accepted solution]

I believe you need is switch statement.

Check this link for more details: https://www.mssqltips.com/sqlservertip/7574/dax-case-statement-dax-if-switch/

and search for text "DAX SWITCH(TRUE())" and read from there.

I tried to understand and came with below syntax. Not sure whether it works correctly or not, give a try...

Your Column = 
SWITCH (
    TRUE (),
    Table1[Value] < Table1[Target], "Orange",
    Table1[Value] >= Table1[Target] && Table1[Value] < Table1[High], "Green",
    Table1[Value] >= Table1[High] && Table1[Value] < Table1[Purple], "Red",
    Table1[Value] >= Table1[Purple], "Purple",
    "0"
)

 

Output:

[samc_26]

Thank you so much too @sevenhills, I nearly fell asleep at my laptop last night trying to work this out for my job, I'm quite stubborn but this was beating me! 

[gadielsolis]

Hello,

Please try the following DAX:

Hopw this helps