How to Use Case Statement in Power BI?

This Power BI tutorial will help you work with the Power BI measure case statement.

In this tutorial, we will learn what the Power BI measure case statement is and how to use case statement in Power BI.

Power BI Measure Case Statement

In Power BI, a measure case statement is a way to create a calculated measure that behaves differently based on specific conditions. It’s similar to switch case statements in programming languages like JavaScript or C#.

A simple method to employ a case statement in Power BI is utilizing the SWITCH function in DAX.

Power BI Switch function syntax:

SWITCH(<expression>, <value>, <result>[, <value>, <result>]…[, <else>])

Where:

  • expression = Provide the DAX expression for the column that needs evaluation.
  • value = Give logical value to compare with the results of the expression.
  • result = If the logical test evaluates to TRUE, what should be the outcome?
  • else = If the logical test evaluates to FALSE, what should be the outcome?

Create Power BI Measure Case Statement

Here, we see how to create a Power BI measure case statement.

Example:

Let’s say you have a Power BI dataset containing information about sales transactions. You want to create a measure called “Sales Performance” that categorizes the performance of sales representatives into three groups: “Excellent,” “Good,” and “Needs Improvement,” based on their total sales amount.

For consider the above example, we have a SharePoint list (Sales Transaction) that contains below columns with various data types:

ColumnsData Types
EpmloyeeIDNumber
EmployeeNameSingle line of text
TotalSalesCurrency
measure using case statement in Power BI

Follow the below steps to create a Power BI measure case statement:

1. Open Power BI Desktop and load the data. Then, you see data in the Data pane.

power bi measure case statement

2. To create a total sales measure, navigate to the Home tab and click ‘New measure.’

switch statement in power bi example

3. In the formula bar, click the below expression. Then click Commit.

Total Sales = SUM('Sales Transaction'[TotalSales])

Where:

  • Total Sales = Name of the Measure
  • SUM = DAX Function
  • Sales Transaction = Name of the Table
  • TotalSales = Name of the Column
How to Use Switch in Power BI

4. Now, we create a Sales Performance measure, navigate to the Home tab, and click ‘New measure.’

switch statement in power bi example

5. In the formula bar, click the below expression. Then click Commit.

Sales Performance = 
    SWITCH(
        TRUE(),
        [Total Sales] > 100000, "Excellent",
        [Total Sales] > 50000, "Good",
        "Needs Improvement"
    )

Where:

  • Total Sales is an existing measure that calculates the total sales amount for each sales representative.
  • The SWITCH function evaluates each condition sequentially and returns the corresponding result when a condition is met.
  • TRUE() acts as a placeholder condition that always evaluates to true.
  • If the total sales amount is greater than 100,000, the measure returns “Excellent.”
  • If the total sales amount is between 50,000 and 100,000, the measure returns “Good.”
  • If the total sales amount is less than or equal to 50,000, the measure returns “Needs Improvement.”
power bi measure switch selected value

6. Under the Home tab, expand Visual gallery(black box) -> Click the Table visual.

power bi measure switch

7. Then, use the +Add data option to add the EmployeeID, EmployeeName, Total Sales, and Sales Performance into the Columns field.

power bi switch measure

8. After that, you can see Sales Performance based on sales in the Power BI table visual.

Create Power BI Measure Case Statement

This way, you can create and use measure case statements in Power BI.

Example – 2

Suppose you’re the manager of a sales team, and you want to incentivize your employees based on their sales performance like:

If the total sales amount exceeds $100,000, the bonus is 10% of the total sales amount. If the total sales amount falls between $50,000 and $100,000, the bonus is 5% of the total sales amount. If the total sales amount is less than or equal to $50,000, the bonus is 2% of the total sales amount.

Here we see how we can use a switch case statement in a measure to calculate bonuses for employees in Power BI:

1. Navigate to the Home tab and click ‘New measure.’

switch statement in power bi example

2. In the formula bar, click the below expression. Then click Commit.

Bonus = 
SWITCH(
    TRUE(),
    [Total Sales] > 100000, [Total Sales] * 0.1,
    [Total Sales] > 50000, [Total Sales] * 0.05,
    [Total Sales] <= 50000, [Total Sales] * 0.02
)

Where:

  • Total Sales is an existing measure that calculates the total sales amount for each employee.
  • The SWITCH function evaluates each condition sequentially and returns the corresponding bonus amount when a condition is met.
  • TRUE() acts as a placeholder condition that always evaluates to true.
  • If the total sales amount is greater than $100,000, the bonus is 10% of the total sales amount.
  • If the total sales amount is between $50,000 and $100,000, the bonus is 5% of the total sales amount.
  • If the total sales amount is less than or equal to $50,000, the bonus is 2% of the total sales amount.
switch measure power bi

3. Next, utilize the ‘+ Add data‘ option to include the Bonus in the Columns section of the Power BI table visual.

Power bi measure switch statement

4. After that, you can see the Bonus column in the Power BI table visual based on sales.

Power BI Measure Case Statement with Example

This way, you can use Power BI to measure case statements.

Conclusion

I hope with a case statement, we can handle multiple conditions and their corresponding outcomes within a single expression.

In this tutorial, we covered the concept of a case statement in Power BI measures, walked through creating a case statement in Power BI measures, and how to use it in various scenarios.

Additionally, you may like some more Power BI tutorials: