How to Easily Write a Multiple IF Statement in Power BI using the SWITCH Function
Power BI is an extremely powerful tool to create visuals out of raw data and hence it is very important to know how to manipulate the data as per the requirement of the business. Previously, I have talked about the Calculate function and how it works in my other blog which you will find here.
Today I want to talk about how to use the Switch Function to write multiple IF Statements without breaking a sweat!
Data Analysis Expression, or more commonly referred to as DAX, is a Power BI native language which is employed in Power BI to manipulate data and write complex formulas to serve the purpose of the report. You might be wondering - how come we cannot use the regular conditional function, IF Statement, similar to what we use in Excel?
The reason is something most of us have probably encountered- it becomes quite challenging to write a nested IF statement that has more than three conditions. In such cases, it is recommended to use the Switch function instead to keep the code simple and easy to write and maintain.
In this blog, I will take you through an in-depth tutorial of the Switch function and explore its features and benefits compared to the alternative. By the end of the blog, I hope you will gain a thorough understanding of the Switch function and be equipped with the skills required to implement it in the next Power BI dashboard that you build.
What Is The Switch Function?
The switch function evaluates an expression across a list of values. It returns different results depending on whether the expression matches the value. As mentioned already, the SWITCH function can be used in place of writing nested IF statements.
Here is the basic syntax of the Switch function:
SWITCH(<expression>, <value>, <result>[, <value>, <result>]…[, <else>])
Why Use The Switch Function Instead Of The IF Statement?
The biggest drawback of the IF Statement is the lack of readability and maintenance as soon as the number of conditions exceeds two or three. As the number of conditions increase, nesting them becomes must and this leads to challenges in terms of reading the statement and may cause errors while writing the piece of code.
This is not the only issue with the IF Statement - performance also takes a hit especially when the report in Power BI has numerous such nested IF Statements. This is where the Switch function can come in handy and offers a superior approach for expressing multiple conditions.
Example of the Switch Function in Power BI
Here is an example of how we can use the Switch function in a Power BI dashboard or report that you create. Let’s take a simple example first - we have data which have the months of the year written as “Jan”, “Feb”, “Mar” and so on. To repurpose this to the full name of the month we can use a simple Switch statement as such:
Month_of_Year =
SWITCH(
True(),
Calendar[Month]="Jan", "January",
Calendar[Month] ="Feb", "February",
Calendar[Month] ="Mar", "March",
Calendar[Month] ="Apr", "April",
Calendar[Month] ="Jun", "June",
Calendar[Month] ="Jul", "July",
Calendar[Month] ="Aug", "August",
Calendar[Month] ="Sep", "September",
Calendar[Month] ="Oct", "October",
Calendar[Month] ="Nov", "November",
Calendar[Month] ="Dec", "December",
ISBLANK(Calendar[Month]),"null"
)
In this way, we can easily manipulate the data to have the full name of the month.
Let us take another example where we write the same logic using the IF statement and the Switch Function. Let’s say we want to categorize products based on the sales price listed in our database. Items priced above $1000 will be grouped as high priced products, $500 to $999 grouped as medium priced products and anything under the price of $500 will be Low priced product.
Here is how we can write it using the IF Statement:
Price Category =
IF(
'Product'[Sales Price] < 500,
"Low",
IF(
'Product'[Sales Price] < 1000,
"Medium",
"High"
)
)
Notice how difficult the code looks and is to read. Instead if we use a Switch Statement, the code becomes much easier to read and comprehend.
Price Category =
SWITCH(
'Product'[Sales Price] < 500, "Low",
'Product'[Sales Price] < 1000, "Medium",
"High"
)
Here is another example of how we can evaluate inequalities using the Switch Function. For instance, we want to show different outputs depending on the ROI value. In Power BI we can modify this data by using the following piece of code snippet:
ROI Range = SWITCH(
TRUE(),
AND([ROI]>=0, [ROI]<=0.6), "Between 0% and 60%",
AND([ROI]>0.60, [ROI]<=0.7), "Between 60% and 70%",
AND([ROI]>0.70, [ROI]<=0.8), "Between 70% and 80%",
AND([ROI]>0.80, [ROI]<=0.9), "Between 80% and 90%",
AND([ROI]>0.90, [ROI]<=1.0), "Between 90% and 100%",
"greater than 100%"
)
Closing Remarks
In this blog, we have gone through how the Switch function can be used to effectively write a multiple IF Statement without going through the hassle of writing a nested IF Statement. This means now we have the flexibility of using either of the approaches. Don’t underestimate the functionality of an IF statement in cases where we only have one or two conditions - If statements can be easy to implement logically in these cases.
However, as discussed in the blog, the versatility of the Switch function can come in very handy in certain scenarios so in your next project don’t forget to use this when the opportunity arises. Remember, the best way to learn a new tool is to implement this in real world practice. So start practicing and build feature rich dashboards that can help take your portfolio to the next level!
FAQs
What is the difference between Switch and If in Power BI?
The Switch Function allows for easy to read DAX statements instead of the complicated nested IF Statements for multiple condition formulas. Unlike nested IF statements, the SWITCH function condenses multiple conditions into a single function, leading to cleaner and simpler DAX expressions that are easier to read and maintain.
Is it better to use If or Switch?
For statements which have only one or two conditions, it might be a better idea to use a simple IF Statement. However, in a scenario where we have multiple conditions, it is best practice to use a Switch function.
