How to Write a CASE Statement in Power BI DAX

If you come from an SQL background or are used to conditional logic in programming, you might find yourself looking for a CASE statement in Power BI's DAX language. While DAX doesn't have a function named CASE, it offers a powerful and often more elegant alternative: the SWITCH function. This article will show you exactly how to use SWITCH to perform conditional logic, categorize your data, and create more insightful reports, just like you would with a traditional CASE statement.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Why You Need Conditional Logic in Power BI

Before jumping into the syntax, let's talk about the "why." Almost every meaningful report involves some form of data categorization. Raw data is often just numbers or codes, but to tell a story, you need to group that data into meaningful buckets. This is where conditional logic comes in.

Here are a few common scenarios where you'd need a CASE statement (or its DAX equivalent):

• Categorizing Sales Performance: Grouping individual sales transactions into tiers like "High Value," "Medium Value," and "Low Value."
• Customer Segmentation: Classifying customers based on their purchase frequency, such as "Frequent Buyer," "Occasional," or "One-Time Shopper."
• Translating Status Codes: Converting cryptic numerical codes (e.g., Status 1, 2, 3) into human-readable text like "Pending," "Shipped," and "Delivered."
• Creating Age Groups: Bucketing employees or customers into age ranges (e.g., "18-25," "26-35," "36-45").

Without this ability to segment and label your data, your reports and dashboards would be less intuitive and much harder to interpret.

Meet Your New Best Friend: The SWITCH Function

DAX's answer to the CASE statement is the SWITCH function. It's designed to replace long, clunky nested IF statements, which were the old way of handling multiple conditions. While you can still use nested IFs, SWITCH is cleaner, easier to read, and often more efficient.

The SWITCH function checks an expression against a series of values and returns a corresponding result when it finds a match. It’s incredibly flexible and comes in two main flavors.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

The Anatomy of SWITCH

The basic syntax for the SWITCH function looks like this:

SWITCH(<,expression>,, <value1>,, <result1>,, [<value2>,, <result2>,, ...],, [<,else>,])

Let’s break that down:

• <,expression>: This is the value that will be evaluated. For example, it could be a column containing a status code, like 'Orders'[StatusID].
• <value1>: The first value you want to check the expression against. For example, 1.
• <result1>: The result to return if the expression matches value1. For example, "Pending".
• [<value2>,, <result2>,, ...]: You can add as many value-result pairs as you need. For example, 2, "Shipped", 3, "Delivered", etc.
• [<,else>: An optional default value to return if the expression doesn't match any of the specified values. This is like the ELSE in a CASE statement and is a best practice to include.

SWITCH(TRUE()): The All-Purpose Pattern

While the basic syntax is great for matching a single expression to a list of exact values, the real power comes from a popular pattern: SWITCH(TRUE()). This variation allows you to evaluate a list of different logical conditions, just like a "searched" CASE statement in SQL.

In this pattern, the "expression" is simply the Boolean value TRUE(). You then provide a series of conditions. DAX evaluates each condition one by one, and SWITCH will return the result for the first condition that evaluates to TRUE. The order of your conditions is crucial here.

The syntax looks like this:

SWITCH(TRUE(), <,condition1>,, <result1>,, [<,condition2>,, <result2>,, ...],, [<,else>])

This is the structure you'll use most often for tasks like grouping numerical data into ranges.

Step-by-Step Guide: How to Write Your First SWITCH Statement

Let's walk through a practical example. Imagine you have a 'Sales' table with a 'Revenue' column, and you want to create a new calculated column to categorize each sale as "High," "Medium," or "Low."

1. Navigate to Your Data

In Power BI Desktop, go to the Data view by clicking the table icon on the left sidebar. Select the table you want to add the new column to, in this case, our 'Sales' table.

2. Create a New Calculated Column

From the ribbon at the top, under the "Table tools" tab (or "Column tools"), click on New column. This will open up the formula bar where you can write your DAX expression.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

3. Write the DAX Formula

We'll use the SWITCH(TRUE()) pattern because we're evaluating ranges of values, not a single exact value. Type the following formula into the formula bar:

Sales Tier = SWITCH ( TRUE(), Sales[Revenue] >= 5000, "High", Sales[Revenue] >= 1000, "Medium", "Low" )

Let’s analyze this formula:

• Sales Tier = defines the name of our new column.
• SWITCH(TRUE(), ... ) tells DAX to evaluate the following list of conditions.
• Sales[Revenue] >= 5000, "High", DAX checks this first. If a sale's revenue is 5000 or more, it labels it as "High" and stops evaluating this row.
• Sales[Revenue] >= 1000, "Medium", This is checked only if the first condition was false. If revenue is between 1000 and 4999.99, it's labeled "Medium."
• "Low": This is our default (or else) value. If neither of the first two conditions is met (meaning the revenue is less than 1000), it's automatically labeled "Low."

4. Press Enter and Review

Once you press Enter, Power BI will compute the logic for every row in your table. You will see your new "Sales Tier" column populated with the labels "High," "Medium," or "Low". Now you can use this column in your reports — as a slicer, a legend in a chart, or in the rows of a matrix.

More Practical Examples of SWITCH

The more you use SWITCH, the more you'll realize its versatility. Here are a few more common business scenarios.

Example 1: Mapping Priority Codes to Text

Imagine you have a 'Tickets' table with a Priority column containing numbers 1, 2, and 3. Here’s how you'd translate them:

Priority Level = SWITCH( Tickets[Priority], 1, "High", 2, "Medium", 3, "Low", "Undefined" )

This is a classic use of the basic SWITCH syntax where you are checking one expression (Tickets[Priority]) against a list of exact values.

Example 2: Dynamic Customer Aging

Let's classify customers based on when they last made a purchase. We want to see who is "Active," "At Risk," or "Inactive."

Customer Status = SWITCH( TRUE(), 'Customers'[Last Purchase Date] >= TODAY() - 30, "Active (Last 30 Days)", 'Customers'[Last Purchase Date] >= TODAY() - 90, "At Risk (3-90 Days)", "Inactive (90+ Days)" )

This example demonstrates how you can incorporate other DAX functions like TODAY() inside a SWITCH statement for powerful, dynamic calculations that always stay up-to-date.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Example 3: Categorizing Product Names with Text Functions

Sometimes you need to categorize based on keywords in a text field, like a product name.

Product Group = SWITCH ( TRUE(), CONTAINSSTRING('Products'[Product Name], "Laptop"), "Computers", CONTAINSSTRING('Products'[Product Name], "Keyboard"), "Accessories", CONTAINSSTRING('Products'[Product Name], "Monitor"), "Displays", "Other" )

Here, we use CONTAINSSTRING() to check for text within the product name. This is a great way to tidy up messy data without having to modify the original source.

Best Practices and Common Mistakes

As you get comfortable with SWITCH, keep these tips in mind to avoid common issues:

• Order Matters: With the SWITCH(TRUE()) pattern, conditions are evaluated in the order you write them. Always place your most specific conditions first. For instance, > 5000 must come before > 1000, otherwise, everything over 5000 would be incorrectly labeled as "Medium."
• Include a Default Value: Always provide an else value at the end. It prevents unexpected blank results and helps you catch data that doesn't fit your defined categories.
• Calculated Column vs. Measure: Use SWITCH in a calculated column when you want a static attribute for an entire row that you can use to filter, slice, or group by in visualizations. Use it in a measure when the calculation needs to be dynamic and dependent on the context of the filters applied in the report (e.g., in a chart or table).
• Keep It Readable: Use line breaks and indentation, just like in the examples. It makes complex logic much easier for you and your colleagues to read and debug later.

Final Thoughts

While DAX may not have a CASE statement by that specific name, the SWITCH function is a more-than-capable replacement that is central to building effective Power BI reports. By mastering both its basic form and the powerful SWITCH(TRUE()) pattern, you can cleanly categorize your data, transform codes into meaningful text, and bring valuable context to your analytics.

Of course, becoming proficient with DAX syntax takes time and practice. Manually building logic like SWITCH statements is one of those routine tasks that can consume hours. To speed up this process, we designed Graphed to handle the heavy lifting for you. Instead of writing formulas, you can connect your data sources and simply ask in plain English: "Group my customers into Active, At Risk, and Inactive based on their last purchase date." We turn your questions into instant, real-time visualizations, letting you get straight to the insights without spending time stuck on the syntax.