Conditional expressions are one of the most commonly used expressions in any language as well as DAX. However, in DAX, if you have multiple IF THEN expressions, there is an easier way of doing it; using a function called SWITCH, this blog is about how you can use switch function in DAX and Power BI to write a conditional expression.
Multiple IF Statements
If you ever need to write multiple IF statements in DAX, then you know that it makes the expressions hard to read. Here is an example of an expression with one IF statement:
Back Color = IF( SELECTEDVALUE(DimCustomer[EnglishEducation])="Bachelors", "Green", "White")
The expression above returns Green as the background color if the EnglishEducation is Bachelors, otherwise, White, here it is used as the conditional formatting:
If you don’t know how to set the background color of a visual in Power BI based on a value from a measure, read my article here about the step by step guide.
Now, if you want to add more IF statements, this becomes getting hard to read;
This is only for three of those values, you can imagine how the expression would be if we have five values, or what if we have even more!
The Switch is a very simple and efficient function in DAX (and many other languages) to help writing multiple IF statements much easier, Switch is written in this way:
SWITCH( <expression>, <value 1>,<result 1>, <value 2>,<result 2>, ... ,<else> )
If we want to write the expression above using Switch, it would look like this:
Back Color = SWITCH( SELECTEDVALUE(DimCustomer[EnglishEducation]), "Bachelors","Green", "High School","Red", "Partial High School","Tan", "Graduate Degree","Yellow", "White" )
You can see that even I’ve added one more condition in the expression above, and it is still much simpler than writing many IF statements.
Using SWITCH for Not Equal Criteria (Between, Greater, etc)
Using Switch for conditions that the value is EQUAL to something is simple (like what you have seen in the above). However, using SWITCH when the criteria are NOT EQUAL is a bit tricky. Here is a method that works:
Back Color = SWITCH( TRUE(), [Sales]>8000000,"Green", [Sales]>5000000,"Tan", [Sales]>3000000,"Yellow", [Sales]<2000000,"Red", "White" )
Replacing the expression with TRUE, and the value of that with a conditional expression means that you get the same output, but this time, you can write a condition that can be greater than, less than or even between values.
I hope you use SWITCH in your statements instead of multiple IF statements much easier with this short blog post help. The techniques above, especially the last one is what I use a lot in my expressions.