DAX CASE Statement Functionality with IF, SWITCH and SWITCH True

By:   |   Updated: 2023-03-02   |   Comments (5)   |   Related: > Power BI


Problem

The CASE expression is one of the most valuable tools in your T-SQL toolbox. I use it in almost every query I write. However, there isn't a direct equivalent of CASE in DAX. Since it's a different language entirely, I don't expect it. As my grandmother used to say, I am not surprised, just disappointed. If you don't know, DAX (Data Analysis Expressions) is a language for creating custom calculations and aggregations in Power Pivot, Power BI, and other data analysis tools. Two functions in DAX come close to replicating the functionality but come with limitations. Which one of these functions should you use? Please stay tuned.

Solution

This article will look at the CASE expression and specific situations where you would use it. Continuing, we'll uncover two functions in DAX with similar functionality. I'll review a few examples of the CASE expression in T-SQL and see if we can translate them to DAX. Finally, a function for replicating a CASE expression will be recommended.

What is a CASE Expression?

I couldn't even begin to describe when I started using CASE. I like to imagine it was the first thing I typed after SELECT. Somewhere along the lines, I developed a habit of referring to CASE as both a statement and an expression. For the sake of your sanity, I'll use the term expression. Microsoft defines CASE on its website as an expression that "evaluates a list of conditions and returns one of multiple possible result expressions." Put simply: we provide CASE with an expression or column and instructions of what we want to be returned if conditions are met. Fun fact: you can nest CASE 10 levels deep. I don't think I've tried that to see what error message SQL returns. You earn bonus points for trying it and listing the error in the comments below.

Let's look at an example.

DROP TABLE IF EXISTS #Temperature;

CREATE TABLE #Temperature
(
    [RecordedDate] DATE NOT NULL,
    [DegreeInFahrenheit] DECIMAL(4, 2) NULL
);

INSERT INTO #Temperature
(
    [RecordedDate],
    [DegreeInFahrenheit]
)
VALUES
('01-01-2023', '32.60'),
('01-02-2023', '31.20'),
('01-03-2023', '42.00'),
('01-04-2023', '44.60'),
('01-05-2023', '39.40'),
('01-06-2023', '31.70'),
('01-07-2023', NULL),
('01-08-2023', '21.20'),
('01-09-2023', '19.15'),
('01-10-2023', '07.90');

SELECT RecordedDate,
       DegreeInFahrenheit,
       CASE
           WHEN DegreeInFahrenheit > '40.00' THEN
               'Hot Weather Ahead'
           WHEN DegreeInFahrenheit < '32.00' THEN
               'It''s too cold outside!'
           WHEN DegreeInFahrenheit IS NULL THEN
               'Why didn''t you record this?'
           ELSE
               'Sounds about right'
       END AS Weather
FROM #Temperature;
GO
CASE example output

An important point is that CASE stops when it finds the first true value. For example, if you have rows that would pass multiple condition checks, the first one in the list wins out. Please see the simple example below.

SELECT RecordedDate,
       DegreeInFahrenheit,
       CASE
           WHEN DegreeInFahrenheit > '40.00' THEN
               'Hot Weather Ahead'
           WHEN DegreeInFahrenheit > '41.00' THEN
               'Does this one show up?'
           WHEN DegreeInFahrenheit < '32.00' THEN
               'It''s too cold outside!'
           WHEN DegreeInFahrenheit IS NULL THEN
               'Why didn''t you record this?'
           ELSE
               'Sounds about right'
       END AS Weather
FROM #Temperature;

In the code above, when the temperature is greater than 40, which one does SQL use? If you guessed the first one, you are correct.

You can also use CASE in an ORDER BY clause. I've only done this when sorting by multiple values, and NULLs come into play. Plus, I'm a big believer in sorting outside of SQL Server.

Does CASE Work in DAX?

This article began by noting that DAX has no direct CASE equivalent. I've tried typing in CASE, but the editor always displays the red squiggly line. Most reports I design use direct query and have SQL Server as a data source. So I can easily handle the transformation outside of DAX. However, I do run into situations where that's not an option. This requirement led me to find a CASE alternative in DAX.

Alternatives to CASE in DAX

DAX IF Statement

The first and most obvious alternative is the IF() function. Microsoft defines IF() as a function that "checks a condition, and returns one value when it's TRUE, otherwise it returns a second value." I imagine the concept of inputting a value and getting a result back if its true dates to the dawn of programming. I've included a simple example below.

IF (
    SomeExpression = "SomeValue",
    "Value if true",
    "Value if false"
)

If you're only checking one condition, maybe verifying if an expression is NULL, IF() works perfectly. However, if you need to check multiple conditions, things get complicated. You'll need to start nesting the function. For example, if you wanted to replicate the original CASE expression above, it would look like this:

Weather =
IF (
    ISBLANK ( Temperature[DegreeInFahrenheit] ),
    "Why didn't you record this?",
    IF (
        Temperature[DegreeInFahrenheit] > 40.00,
        "Hot Weather Ahead",
        IF (
            Temperature[DegreeInFahrenheit] < 32.00,
            "It's too cold outside!",
            "Sounds about right"
        )
    )
)
Replicate the original CASE expression by nesting

The code above isn't bad, but we're only three levels deep. I don't know about you, but nesting a function several layers deep is never a good way to start my day. There must be a better way.

DAX SWITCH()

Most times, I'm not checking a single condition. I needed to find something else. That's when I discovered the SWITCH() function. It just so happens that C# has a switch statement as well. Back to DAX, Microsoft defines SWITCH() as a function that "evaluates an expression against a list of values and returns one of multiple possible result expressions." The definition appears closer to that of the CASE expression. Let's look at an example.

SWITCH (
    [SomeExpression],
    1, "Yes",
    2, "No",
    3, "Maybe",
    "No Idea"
)

If we are checking for equality, SWITCH() performs the job. However, what if you use another type of operator, like a greater or less than, as in our original CASE expression?

I'm back again to wishing I had CASE. However, I'm not giving up hope. As Yoda wisely said, 'there is another.'

DAX SWITCH(TRUE())

The last function we'll look at combines TRUE() and SWITCH(). As the name implies, TRUE() always returns TRUE.

How can we integrate these two functions? The example below demonstrates trying to replicate the original CASE expression using TRUE() and SWITCH().

Weather =
SWITCH (
    TRUE (),
    Temperature[DegreeInFahrenheit] = BLANK (), "Why didn't you record this?",
    Temperature[DegreeInFahrenheit] > 40.00, "Hot Weather Ahead",
    Temperature[DegreeInFahrenheit] < 32.00, "It's too cold outside",
    "Sounds about right"
)
Combining TRUE() and SWITCH()

Now those are the results I wanted to see; mission accomplished!

Which One to Choose?

Have you ever gone to an ice cream shop and been presented with dozens of flavors? Picking your favorite one is hard; there are too many options. The fear of missing out is intense. The good thing about finding a workable alternative to CASE in DAX is that you have fewer choices. I generally go with the SWITCH(TRUE()) combination. If I perform one logic check, I might go with IF(). Ultimately, if you like nested IF() functions and they don't upset your co-workers, keep doing your thing.

Key Points

  • A perfect replacement doesn't exist for the SQL expression CASE in DAX. However, a couple of functions come close.
  • IF() and SWITCH() are two recommended functions for getting the same results as a CASE expression.
  • Nesting several IF() functions can be hard to read, especially when working with a team of developers.
  • SWITCH() checks for equality matches. However, you can incorporate SWITCH(TRUE)) for even more flexibility.
  • You can include SWITCH(TRUE()) inside of an IF() function for building more complex logic.
Next Steps

Learn more about Power BI in this 3 hour training course.


Click here to start the Power BI course






sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Jared Westover Jared Westover is a passionate technology specialist at Crowe, helping to build data solutions. For the past two decades, he has focused on SQL Server, Azure, Power BI, and Oracle. Besides writing for MSSQLTips.com, he has published 12 Pluralsight courses about SQL Server and Reporting Services. Jared enjoys reading and spending time with his wife and three sons when he's not trying to make queries go faster.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2023-03-02

Comments For This Article




Thursday, July 25, 2024 - 6:14:23 AM - Jared Westover Back To Top (92409)
@Harrison Bergeron

Thank you for reading and providing the tip about a lookup table!

Wednesday, June 19, 2024 - 9:55:58 AM - Harrison Bergeron Back To Top (92324)
While I understand that this is a simple example I have found that using lookup tables instead of a bunch of Case Statements is preferable for understanding and maintainability. Especially in a situation where there are many potential case checks that must be done. I just link the value I am testing to the lookup table using a Join and output the lookup conversion.

Monday, April 3, 2023 - 9:50:07 AM - Lutz Back To Top (91078)
You are missing a couple of important things. SWITCH is "syntax sugar" for nested IF statements. The Vertipaq query plan is the same in the vast majority of cases. Yes, it improves readability.

The really fun feature of SWITCH is when you use it like this : SWITCH(FALSE(),...,...)

Thursday, March 2, 2023 - 11:54:38 AM - Jared Westover Back To Top (90972)
@James

Hahaha. I hoped Clippy would appear and say something witty. :) Thanks for reading and posting James!

Thursday, March 2, 2023 - 10:17:05 AM - James Back To Top (90971)
Nesting Case statements 11 deep was mildy anti-climactic:

Msg 125, Level 15, State 3, Line 1
Case expressions may only be nested to level 10.














get free sql tips
agree to terms