Best Way - Combining IF and OR Functions

mbraun · July 15, 2019, 6:09pm

Just curious what is the best way to set up this DAX Formula… I need to check 5 columns for examples to see if any of them are equaled = “YES”, If any of them equal “YES” then the Calculated Column equals Yes else No.

Column1
Column2
Column3
Column4
Column5
Calculated Column

Thought I could have set it up with the following put it wasn’t working…

Calculated Column = 
if (or(Column1="Yes",Column2="Yes",Column3="yes"), "Yes","No")

JarrettM · July 15, 2019, 7:40pm

Probably your best solution is to use the SWITCH statement. I will give an example of how to do this in a measure & a calculated column.

Your measure would look something like this:

Measure=
    SWITCH(TRUE(),
    SELECTEDVALUE([Column 1])= "Yes","Yes",
    SELECTEDVALUE([Column 2])= "Yes","Yes",
    SELECTEDVALUE([Column 3)= "Yes","Yes",
    SELECTEDVALUE([Column 4])= "Yes","Yes",
    SELECTEDVALUE([Column 5])= "Yes","Yes",
    "No"

Calculated Column look like this:

Calculated Column=
IF( YourTable[Column 1]= "Yes" || YourTable[Column 2]= "Yes" || YourTable[Column 3]= "Yes" || YourTable[Column 4]= "Yes" || YourTable[Column 5]= "Yes", "Yes", "No")

Here is a video from Sam about SWITCH

Thanks