Write Conditional Statement Using SWITCH in DAX and Power BI

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

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:

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:

If we want to write the expression above using Switch, it would look like this:

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:

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

FacebooktwitterredditpinterestlinkedintumblrmailFacebooktwitterredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
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 nine 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.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
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.

Leave a Reply