How to Write a CASE Statement in Power BI

If you're coming from a SQL background or trying to group your data into custom categories, you've probably searched for how to write a CASE statement in Power BI. The good news is that creating conditional logic is straightforward in Power BI, though it uses a different name. This tutorial will walk you through the two best methods for recreating CASE statement logic to add powerful new columns to your data model.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

CASE in Power BI: Understanding Your Options

In the world of SQL, the CASE WHEN... THEN... END statement is a go-to for creating conditional logic. It lets you define a series of conditions and return a specific value when a condition is met. In Power BI, this same outcome is achieved primarily in two ways:

• The SWITCH Function: This is a DAX function that acts as Power BI's direct equivalent to a CASE statement. It’s clean, efficient for handling multiple conditions, and is perfect for creating calculated columns or measures directly within your data model.
• The Conditional Column Feature: This is a user-friendly, graphical interface within the Power Query Editor (Power BI's data transformation environment). It allows you to build the same conditional logic without writing a single line of code.

While you can also use nested IF statements, they tend to become complicated and hard to read once you have more than two or three conditions. For the sake of clarity and performance, the SWITCH function or the Conditional Column feature are almost always better choices.

When to Create Conditional Columns

So, why would you need to create this kind of logic in the first place? Creating these "grouping" columns helps you turn raw data into meaningful insights. It's a fundamental part of building effective reports.

Here are some common, everyday scenarios:

• Grouping Numerical Values: You might have sales revenue and want to categorize deals as "Small," "Medium," or "Large."
• Categorizing Text Values: You could have a list of US states and want to group them into "West," "Midwest," and "East" regions.
• Replacing Codes: Your data might contain product codes like PROD-001 or PROD-002. A conditional column can replace these with user-friendly names like "Premium Laptop" or "Wireless Mouse."
• Evaluating Dates: You can create columns to flag data as "Recent" or "Aged" based on a date column.

In all these examples, you're transforming existing data into a new, more descriptive dimension that you can use in your charts, slicers, and tables. Let’s build one now with both methods.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Method 1: Using the SWITCH Function in DAX

DAX (Data Analysis Expressions) is the formula language used throughout Power BI. Using a DAX function like SWITCH to create a "Calculated Column" is an excellent way to add conditional logic directly to your data table after it has been loaded into Power BI.

For this example, let’s imagine we have a table named Sales with a numeric [Revenue] column. Our goal is to create a new Deal Size column that categorizes each sale based on its revenue.

Step-by-Step Guide to Using SWITCH

1. Navigate to the Data View: In Power BI Desktop, look at the left-hand pane and click on the "Data" icon (it looks like a small table). This will show you the raw data in your tables.

2. Select Your Table: From the "Fields" pane on the right, select the table you want to add the column to. In our case, we'll click on the Sales table.

3. Create a New Column: With your table selected, a "Table tools" tab will appear at the top in the ribbon. Click on "New column." This action opens a formula bar right above your data table, ready for your DAX formula.

Now, let's look at the syntax for the SWITCH function before we write our formula.

An Important Note on SWITCH Syntax

The standard SWITCH function syntax checks one expression against a list of exact matching values. It looks like this:

SWITCH(<expression>, <value1>, <result1>, <value2>, <result2>, ..., <else_result>)

This is great for replacing text values, like SWITCH('Sales'[State], "CA", "California", "NY", "New York", "Other"). But what if you need to evaluate ranges, like our revenue example (e.g., less than $500)?

To do this, we use a handy trick: SWITCH(TRUE(), ...). This changes the logic so the function looks for the first condition that evaluates to TRUE. This setup lets you write complex conditional logic that handles numerical ranges and comparisons easily.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

Write Your SWITCH(TRUE()) Formula

In the formula bar, we will define our "Deal Size" logic. We want to classify sales as:

• Less than $500: "Small"
• Less than $5,000 (but greater than or equal to $500): "Medium"
• Anything else ($5,000 or more): "Large"

Here’s the DAX formula to accomplish this. Type it into the formula bar:

Deal Size = SWITCH( TRUE(), 'Sales'[Revenue] < 500, "Small", 'Sales'[Revenue] < 5000, "Medium", "Large" )

Let's break that down:

• Deal Size = : This names our new column.
• SWITCH(TRUE(), ... ) : This sets up the function to evaluate a series of true/false conditions.
• 'Sales'[Revenue] < 500, "Small", : This is our first condition. If the revenue for a given row is less than $500, return the text "Small" and stop.
• 'Sales'[Revenue] < 5000, "Medium", : This is the second. If the first condition was false, it checks if revenue is less than $5000. If true, it returns "Medium" and stops. The order is important here!
• "Large" : This is the final else value. If neither of the previous conditions were met, it means the revenue must be $5,000 or greater, so it returns "Large."

Commit the Formula

Press Enter. Power BI will execute the DAX formula for every row in your table, and you will see a new "Deal Size" column appear on the far right of your table.

Congratulations! You've just created a powerful conditional column using DAX. You can now drag the "Deal Size" field into any visual to segment your data.

Method 2: Using Conditional Columns in Power Query

If you prefer a more visual, code-free approach (or you're already doing some data cleaning steps), Power Query is the perfect place to build your conditional column.

Power Query is Power BI's built-in data transformation tool. The changes you make here are applied to your data before it's loaded into the data model.

Step-by-Step Guide to Conditional Columns

1. Open the Power Query Editor: From the "Home" tab in Power BI Desktop's main window, click the "Transform data" button. This will launch the Power Query Editor.

2. Select Your Query: In the "Queries" pane on the left, select the data query (table) you wish to modify. We’ll stick with our Sales example.

3. Find the Conditional Column Button: Click on the "Add Column" tab in the Power Query ribbon at the top. In this tab, you will see an option labeled "Conditional Column." Click it.

4. Define Your Logic in the Dialog Box: A new window will pop up that provides an easy-to-use interface for building your If...Then...Else logic. We will replicate our exact "Deal Size" logic here:

1. New column name: Type "Deal Size".
2. First Clause (If):
3. Add the Second Clause (Else If): Click the "Add clause" button to create another block.
4. Final Clause (Else): In the final Else field at the bottom, enter Large. This will be the value for any row that didn't meet the first two conditions.

Your finished dialog box should look like this. As you build the rules, the interface guides you, making it very hard to make a syntax error.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Click OK

Once you click OK, Power Query will generate the underlying M code for you and add the "Deal Size" column to your table preview.

Close & Apply

Finally, to save your changes and load them into the main Power BI report, click the "Close & Apply" button on the "Home" tab of the Power Query ribbon.

Which Method Should You Choose?

Both methods achieve the same result, so which one is right for you? It depends on your comfort level and where you are in your data modeling process.

• SWITCH() Function (DAX): Choose this method if you are comfortable writing simple formulas and want to add logic to your data after it’s already loaded into Power BI. It's flexible, powerful, and very readable when dealing with multiple distinct options. It's often considered a best practice for business logic that might need to be adjusted later because it lives directly in your data model rather than in the data ingestion step.
• Conditional Columns (Power Query): This method is fantastic for an audience that might not be familiar with coding. It guides you through the process, preventing errors and providing a clear overview of the logic. It's also the best choice if you are performing other data cleaning and shaping steps (like removing columns, changing data types, or splitting text) in Power Query at the same time. This keeps all your transformations in one place.

In short: If you live in the Power BI formula bar, use SWITCH. If you prefer a step-by-step graphical user interface, the Conditional Column feature is your best friend.

Final Thoughts

Creating conditional logic by grouping, categorizing, and recoding data transforms it from a simple table of figures into a rich source for analysis. Power BI’s SWITCH function provides a clean and powerful way to handle multiple conditions that is a clear step up from nested IF statements, while the Conditional Column feature offers a user-friendly, code-free alternative for anyone.

While these tools are a huge improvement over manually classifying data in a spreadsheet, sometimes even this process can slow you down when you just want a quick answer. Learning the nuances of DAX or clicking through menus to build logic takes time. This is where we built Graphed to streamline the entire process. Rather than building the column and then the chart, you handle both in a single step using plain English. You could simply ask it, "Show me revenue by deal size, where deals under $500 are 'Small' and under $5000 are 'Medium'," and get an instant, fully interactive dashboard that’s already grouped your data just the way you wanted.