Switch Function in Power BI
We all know how important logical functions in the field of data analysis and interpretation. When we talk about logical functions “IF” is the father of all the logical functions we use, but not many of us aware that there is an alternative to IF condition in Power BI. Yes, we have an alternative to IF condition i.e. “SWITCH” DAX function in power BI. In this article, we will guide you through the DAX function “SWITCH” in detail.
What Does SWITCH Function Do in Power BI?
SWITCH is a kind of logical function to arrive results based on multiple conditions. So, the power BI in switch function is looking at all the logical conditions and arrives at the result of the logical condition which is TRUE. However, unlike IF conditions, we cannot use complex calculations with SWITCH but good enough function replace nested IF conditions in excel.
Below is the syntax of the SWITCH function in Power BI.
The syntax is unlike IF but we will explain it for you below.
- Expression: This is nothing but the column that we need to evaluate.
- Value1: So, for this, we can apply what is the logical test we need to do against the Expression column.
- Result1: If the “Value1” logical test is TRUE then what should be the result.
- [Value2]: This is the optional parameter if the Value1 logical tests are FALSE then what is the second logical test to evaluate the Expression.
- [Result1]: If the “Value2” logical test is TRUE then what should be the result.
- [Else]: If all the logical tests are FALSE then what is the alternative result needed.
Examples of SWITCH Function in Power BI
Below are examples of the switch function in Power BI. You can download the workbook to use the same file as we used in this example.
Below is the data table we are going to use to demonstrate the SWITCH function in Power BI. You can download the excel workbook and use the same to practice.
We need to arrive at “Appraisal %” based on the rating value available. Below are the criteria to arrive at the Appraisal%.
- If the rating is =5 then appraisal percentage will be 10%.
- If the rating is =4 then appraisal percentage will be 8%.
- If the rating is =3 then appraisal percentage will be 6%.
- If the rating is =2 then appraisal percentage will be 5%.
- If the rating is =1 then appraisal percentage will be 4%.
Ok, upload the data table to Power BI to start the proceedings.
Right-click on the table and choose “New Column” to arrive appraisal % in the new column.
Now name the new column as “Appraisal %”.
Open the SWITCH function in Power BI now.
The first argument is Expression i.e. which column we need to test to arrive appraisal % in the new column. So in this case by testing rating we need to arrive result, so choose the “Rating” column.
Value 1 is nothing but the logical test that we need to apply against the Expression column, so our first logical test is to check whether the rating is =5 or not.
The next argument is Result1 i.e. what is the result if the Value1 logical test is correct. So we need the result as 0.10.
Next is Value2 i.e. if the Value1 logical test is false then what is the second logical test that we need to apply, so we need to test rating =4 or not.
If this logical test is TRUE then Result 2 will be 0.08.
Similarly, the next applies the third logical test.
Ok close the bracket and hit enter key to get the result.
Like this, we can arrive results, but when it comes to logical operator usage we need to include different strategies.
Now look at the below data
From this table, we need to arrive new column as “Quarter”. To arrive in this column we need to test month numbers and below are the criteria.
- If the month number is >9 then quarter is “Q4”.
- If the month number is >6 then quarter is “Q3”.
- If the month number is >3 then quarter is “Q2”.
- If any other month number then quarter is “Q1”.
So, we need to test three conditions and if all the three are FALSE then quarter will be “Q1”. Ok, upload the data table to Power BI to start the proceedings.
Right-click on the table and choose “New Column” to arrive quarters in the new column.
Now name the new column as “Quarter #”.
Choose the SWITCH function.
The first argument is Expression i.e. we need to supply the result logical value TRUE first.
Then by selecting the column, we can use logical tests like the below.
By using the above technique we can use logical operator symbols.
Note: Power BI dashboard file can also be downloaded from the link below and the final output can be viewed.
Things to Remember
- ELSE parameter is used only to get the alternative result.
- We cannot use logical operator symbols like the IF condition but need to use the TRUE or FALSE logical function to use logical operators.
This has been a guide to Power BI SWITCH. Here we learn how to use the SWITCH Function in Power BI along with examples. You may learn more about Power BI from the following articles –