In Power BI, creating custom columns based on multiple conditions is a powerful way to enhance the analysis and presentation of your data. One of the most versatile functions for this purpose is SWITCH, which allows you to apply different transformations or categorizations based on specific values in another column.

In this blog post, we will walk through an example of how to use the SWITCH function to create a custom column that formats data differently depending on the type of information it contains.

Scenario

Let’s imagine we have a table of weekly data that includes various metrics such as:

Column_A	Column_B
Items added this week	3
Total items	610
Members added this week	20
Total members	2,500
Disbursements this week	500,000
Total outstanding balance	9,000,000
Yearly member growth rate	3
Yearly portfolio growth rate	845.56%

We want to create a calculated column that applies different formats to these values:

• Keep values like “Items added this week” and “Members added this week” as integers.
• Format values like “Yearly portfolio growth rate” as percentages.

Step-by-Step Guide

1. Open Power BI and Load Your Data

Start by opening your Power BI project and loading the table where you want to create the custom column. You can either import a dataset or use an existing table.

2. Open Power BI’s Data Tab

Go to the Data View by selecting the Data icon in the left-hand panel. This will allow you to see the raw data from your tables and the transformations you apply.

3. Create a New Calculated Column

Navigate to the Modeling tab and click on New Column. This will open a formula bar where you can create your custom column using DAX (Data Analysis Expressions).

4. Use the SWITCH Function

In the formula bar, type the following DAX expression to create a custom column that applies different formats to your values based on their description:

CustomColumn = 
SWITCH(
    TRUE(),
    'table'[Column_A] = "Yearly portfolio growth rate", 
        FORMAT('table'[Column_B], "Percent"),
    'table'[Column_A] = "Yearly member growth rate", 
        FORMAT('table'[Column_B], "Percent"),
    'table'[Column_A] = "Disbursements this week", 
        FORMAT('table'[Column_B], "#,##0"),
    'table'[Column_A] = "Total outstanding balance", 
        FORMAT('table'[Column_B], "#,##0"),
    FORMAT('table'[Column_B], "General Number")
)

Explanation of the DAX Formula:

• SWITCH: This function evaluates an expression and returns a value that matches the first true condition.
• TRUE(): Used in combination with SWITCH to check multiple conditions.
• 'table'[Column_A]: This refers to the column in your table that contains the descriptions of the metrics (e.g., “Yearly portfolio growth rate”).
• FORMAT: This function formats the values based on the condition.
  • For growth rates, it applies a percentage format.
  • For financial figures like “Disbursements this week”, it applies a comma-separated number format.
  • All other values will use a general number format by default.

5. Apply and View the Results

After entering the formula, hit Enter to apply the new calculated column. You will see your data with the correct formats applied to each value depending on its type.

Conclusion

The SWITCH function in Power BI is incredibly useful for creating custom calculated columns based on multiple conditions. In this example, we used it to apply different formats depending on the type of data, making the results more readable and intuitive. You can further modify this formula to match your specific needs or even extend it to handle additional cases.