Write Conditional Statement Using SWITCH in DAX and Power BI

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!

SWITCH

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.

Video

Reza Rad

Trainer, Consultant, Mentor

Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.