Power BI Switch

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 the IF condition in Power BI. Yes, we have an alternative to the 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 excelNested IF Conditions In ExcelIn Excel, nested if function means using another logical or conditional function with the if function to test multiple conditions. For example, if there are two conditions to be tested, we can use the logical functions AND or OR depending on the situation, or we can use the other conditional functions to test even more ifs inside a single if.read more.

Below is the syntax of the SWITCH function in Power BI.

Power BI SWITCH Formula

The syntax is unlike IF, but we will explain it to 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.
Power-BI-SWITCH-Function

You are free to use this image on your website, templates etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: Power BI Switch (wallstreetmojo.com)

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.

You can download this Power BI SWITCH Excel Template here – Power BI SWITCH Excel Template

Example #1

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.

Power BI SWITCH - Example 1

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 the appraisal percentage will be 10%.
  • If the rating is =4, then the appraisal percentage will be 8%.
  • If the rating is =3 then the appraisal percentage will be 6%.
  • If the rating is =2, then the appraisal percentage will be 5%.
  • If the rating is =1, then the appraisal percentage will be 4%.

Ok, upload the data table to Power BI to start the proceedings.

Power BI SWITCH - Example 1-1

Right-click on the table and choose “New Column” to arrive appraisal % in the new column.

Power BI SWITCH - Example 1-2

Now name the new column as “Appraisal %.”

Power BI SWITCH - Example 1-3

Open the SWITCH function in Power BI now.

Power BI SWITCH - Example 1-4

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.

Power BI SWITCH - Example 1-5

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.

Power BI SWITCH - Example 1-6

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.

Power BI SWITCH - Example 1-7

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.

Power BI SWITCH - Example 1-9

If this logical test is TRUE, then Result 2 will be 0.08.

Power BI SWITCH - Example 1-8

Similarly, the next applies the third logical test.

Power BI SWITCH - Example 1-9

Ok, close the bracket and hit the enter key to get the result.

Power BI SWITCH - Example 1-10

Like this, we can arrive results, but when it comes to logical operator usage, we need to include different strategies.

Example #2

Now look at the below data

Example 2

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 the quarter is “Q4”.
  • If the month number is >6, then the quarter is “Q3”.
  • If the month number is >3, then the quarter is “Q2”.
  • If any other month number, then the quarter is “Q1”.

So, we need to test three conditions, and if all the three are FALSE, then the quarter will be “Q1”. Ok, upload the data table to Power BI to start the proceedings.

Example 2-1

Right-click on the table and choose “New Column” to arrive quarters in the new column.

Example 2-2

Now name the new column as “Quarter #.”

Example 2-3

Choose the SWITCH function.

Example 2-4

The first argument is Expression, i.e., we need to supply the result logical value TRUE first.

Example 2-5

Then by selecting the column, we can use logical tests like the below.

Example 2-6

By using the above technique, we can use logical operator symbols.

Example 2-7

Note: Power BI dashboard file can also be downloaded from the link below, and the final output can be viewed.

You can download this Power BI SWITCH Template here – Power BI SWITCH Template

Things to Remember

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 –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>