What is SWITCH Function in Power BI?
The Power BI SWITCH function is a logical DAX function that allows you to perform multiple conditional evaluations and returns a result based on the first expression that evaluates to TRUE. Using the Power BI SWITCH statement, you can avoid multiple nested IF statements and create a more structured and readable way to handle multiple conditions.
Table of contents
Key Takeaways
- The SWITCH function is a DAX function in Power BI used for conditional evaluations.
- It evaluates a given expression against multiple conditions and returns a result based on the first match.
- It offers a structured way to handle multiple conditions and can improve code readability.
- The function stops evaluating once a condition is met, and you can provide a default result if no conditions match.
What does SWITCH Function do in Power BI?
The SWITCH function evaluates a given expression against a list of possible values and returns a result based on the first match. It’s beneficial when you have a set of conditions and corresponding values and want to retrieve an effect associated with the first condition that evaluates to TRUE.
The syntax of the Power BI SWITCH function is:
SWITCH(Expression, Value1, Result1, …, [Else])
- Expression – Any DAX expression that returns a singular value. The expression shall be evaluated against multiple values for each row.
- Value1 – A static value that will be matched against the results of the expression.
- Result1 – An expression to be evaluated when the result of the expression matches with the corresponding value.
- Else – An expression to be evaluated when the result of the expression doesn’t match the corresponding value.
Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)
If you want to learn Excel and VBA professionally, then Excel VBA All in One Courses Bundle (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.
How to Use SWITCH Function in Power BI?
Here’s how you can use the SWITCH function in Power BI:
Step 1: Open your Power BI Desktop and load your dataset using the Get data option.
Step 2: Navigate to the Modeling tab and choose the New column or New measure option.
Step 3: Enter the Power BI SWITCH statement, Power BI SWITCH case, or Power BI SWITCH True DAX in the formula bar and click Commit to save the changes.
A sample Power BI SWITCH case example is shown below,
Step 4: Use the calculated columns to create data visualization in Power BI.
Examples of SWITCH Functions in Power BI
In this section, we will use Power BI SWITCH True DAX to create data visualization in Power BI.
Example #1
In this example, we will calculate the Tax applicable for each of the employees according to their Tax bracket using the Power BI SWITCH True DAX using the Employee Dataset dataset. The Employee Dataset contains the employee details along with their salary information. The tax bracket for the calculation purpose will be as per below:
| Salary | Tax Rate |
|---|---|
| < 11000 | 10% |
| Over $11,000 but not over $44,725 | $1,100 plus 12% of the excess over $11,000 |
| Over $44,725 but not over $95,375 | $5,147 plus 22% of the excess over $44,725 |
| Over $95,375 but not over $182,100 | $16,290 plus 24% of the excess over $95,375 |
| Over $182,100 but not over $231,250 | $37,104 plus 32% of the excess over $182,100 |
| Over $231,250 but not over $578,125 | $52,832 plus 35% of the excess over $231,250 |
| Over $578,125 | $174,238.25 plus 37% of the excess over $578,125 |
To use the Power BI SWITCH function, follow the steps highlighted below:
Step 1: Open the Power BI Desktop, import the Employee Dataset dataset using the Get Data option, and click on the Load button. You can choose the Transform Data option to perform data transformation as needed.
Once the data is loaded, you shall be able to view it by navigating to the Fields pane.
Step 2: Navigate to the Fields pane, right-click on the Employee Dataset table, and choose the New column option from the menu.
Step 3: Enter the Power BI SWITCH True DAX in the formula tab.
Here, we have created a calculated column, Tax Applicable, to calculate the Tax liability for each employee.
Tax Applicable = SWITCH(TRUE,’Employee Dataset'[Salary] <= 11000, 0.1, AND(‘Employee Dataset'[Salary] > 11000,’Employee Dataset'[Salary] <=44275),1100 + 0.12 * (‘Employee Dataset'[Salary] – 11000), AND(‘Employee Dataset'[Salary] > 44275,’Employee Dataset'[Salary] <=95375),5147 + 0.22 * (‘Employee Dataset'[Salary] – 44275), AND(‘Employee Dataset'[Salary] > 95375,’Employee Dataset'[Salary] <=182100),16290 + 0.24 * (‘Employee Dataset'[Salary] – 95375), AND(‘Employee Dataset'[Salary] > 182100,’Employee Dataset'[Salary] <=231250),37104 + 0.32 * (‘Employee Dataset'[Salary] – 182100), AND(‘Employee Dataset'[Salary] > 231250,’Employee Dataset'[Salary] <=578125),52832 + 0.35 * (‘Employee Dataset'[Salary] – 231250), ‘Employee Dataset'[Salary] > 578125, 174238.25 + 0.37* (‘Employee Dataset'[Salary] – 578125))
Step 4: Click on the Commit icon to save the changes. It shall create a new column in the data table in excel.
You can see the underlying values for this new column by navigating to the Data view tab.
Step 5: Repeat Step 2 to create a new column on the Employee Dataset table.
Step 6: Enter the Power BI SWITCH True DAX in the formula tab.
Here, we have created a calculated column Net Takehome Salary to calculate the post-tax takehome salary for each of the employees.
Net Takehome Salary = ‘Employee Dataset'[Salary] – ‘Employee Dataset'[Tax Applicable]
Step 7: Click on the Commit icon to save the changes.
Navigate to the Table view to see the underlying values for this new column.
Example #2
In this example, we will calculate whether each of the sales team members has achieved their sales target and the bonus entitlement using the Power Bi SWITCH True DAX using the US_Org_Sales_Employee_Dataset dataset. TheUS_Org_Sales_Employee_Dataset dataset contains the sales employee details along with their sales target information. The bonus entitlement is only for those who have achieved the target, as shown below.
| Org Designation | Bonus % |
|---|---|
| Associate | 10% |
| Senior Associate | 15% |
| Assistant Vice President | 20% |
| Vice President | 25% |
| Executive Director | 30% |
To use the Power BI SWITCH function, follow the steps highlighted below:
Step 1: Open the Power BI Desktop, import the US_Org_Sales_Employee_Dataset dataset using the Get Data option, and click the Load button.
Once the data is loaded, you can view it by navigating to the Fields pane. Note that we have renamed the table to Sales Team Dataset for this demo.
Step 2:
- Navigate to the Fields pane.
- Right-click on the Employee Dataset table.
- Choose the New column option from the menu.
Step 3: Enter the Power BI SWITCH True DAX in the formula tab.
Here, we have created a calculated column Target Achieved? to determine the employees who are eligible for bonus entitlement.
Target Achieved? = SWITCH(TRUE,’Sales Team Dataset'[Sales Achieved] > ‘Sales Team Dataset'[Target Sales], “Yes”, “No”)
Click on the Commit icon to save the changes.
Step 4: Repeat Step 2 to create a new column on the Sales Team Dataset.
Step 5: Enter the Power BI SWITCH True DAX in the formula tab.
Here, we have created a calculated column, Earned
Bonus to calculate the bonus percentage for the eligible employees.
Earned Bonus = SWITCH(TRUE, AND(‘Sales Team Dataset'[Org Designation] = “Associate”, ‘Sales Team Dataset'[Target Achieved?] = “Yes”), 0.1, AND(‘Sales Team Dataset'[Org Designation] = “Senior Associate”, ‘Sales Team Dataset'[Target Achieved?] = “Yes”), 0.15, AND(‘Sales Team Dataset'[Org Designation] = “Associate Vice President”, ‘Sales Team Dataset'[Target Achieved?] = “Yes”), 0.2, AND(‘Sales Team Dataset'[Org Designation] = “Vice President”, ‘Sales Team Dataset'[Target Achieved?] = “Yes”), 0.25, AND(‘Sales Team Dataset'[Org Designation] = “Executive Director”, ‘Sales Team Dataset'[Target Achieved?] = “Yes”), 0.3, 0)
Click on the Commit icon to save the changes.
Step 6: Repeat Step 4 to create a new column on the Sales Team Dataset.
Step 7: Enter the DAX in the formula tab to create the new column.
Here, we have created a Total Compensation column to calculate the total compensation, including the bonus amount for all employees.
Total Compensation = ‘Sales Team Dataset'[Salary] + (‘Sales Team Dataset'[Earned Bonus] * ‘Sales Team Dataset'[Salary])
Click on the Commit icon to save the changes.
Step 8: Navigate to the Data view tab to view the calculated columns and the underlying datasets.
Note you can also use Power BI SWITCH statement to create calculated columns or measures for your visualization requirements.
Important Things to Note
- The SWITCH function follows the order of the condition, and it stops evaluating the remaining values as soon as it finds a matching condition.
- If none of the conditions match, you can provide an optional [Else] at the end.
- You can use any type of expression in the SWITCH function, such as columns, measures, or constants. However, all of them must be of the same type.
- The values in the SWITCH function are case-sensitive. Make sure to match the cases properly.
Frequently Asked Questions (FAQs)
You can switch visuals in Power BI by creating a Power BI switch button. To create a Power BI switch button, follow the steps below:
• Create the visuals you want to switch between on the report canvas and lay them on top of each other.
• Navigate to Insert – Buttons and choose Blank to create blank buttons.
• Navigate to the Format pane for any formatting to be applied to the buttons.
• Navigate to the View tab and click on Bookmarks to add bookmarks.
• Choose the Selection button under the View tab and define the actions for the buttons.
• Specify what attributes you want to hide or show for each button and then save. Post these changes, you can create a Power BI switch button and switch visuals.
Some possible reasons why the SWITCH in Power BI is not working as expected could be:
• Incorrect DAX syntax, relationship issues, or other data-related problems. Make sure your DAX expressions are properly written, and your data model is set up correctly.
• Differences in data types in the result arguments may result in errors. Ensure that the data types in all the results and else arguments are the same in the Power BI SWITCH DAX function.
To switch between Import and DirectQuery modes in Power BI, you’ll need to modify your data source settings:
• Open an existing report in your Power BI Desktop.
• Choose the table in Fields pane and click on Edit Query option to open the Power Query Editor.
• In the Power Query Editor, navigate to Home tab and choose Data source settings or Advanced Editor as per your requirements.
• In the Power Query Editor, under Data source settings, you can choose the dataset for which you want to change the source and then switch between import and DirectQuery.
• Similarly, you can also choose Advanced Editor to switch the data source as well.
• After making the changes, close the Power Query Editor and save your changes.
• If you’ve switched to DirectQuery, you’ll need to design your visuals considering DirectQuery limitations.
In Power BI, you can switch columns and rows in a table visual by using the Matrix visual type. Follow the steps below:
• Open Power BI Desktop and import the dataset.
• Navigate to the Visualizations pane and choose Matrix visual to your report canvas.
• Drag and drop the fields you want to use as rows into the Rows and Columns area of the Matrix visual.
• You can also add data to the Values area to populate the matrix cells.
• Power BI will automatically aggregate data based on your selections.
Recommended Articles
This has been a guide to Power BI SWITCH Function. Here we look how to use Switch DAX function to create data visualization in power bi, with examples. You can learn more from the following articles –
Leave a Reply