By: Jared Westover | Updated: 2023-03-02 | Comments (3) | 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
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"
)
)
)
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"
)
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
- Would you like to learn more about the T-SQL CASE expression? Koen Verbeeck wrote a detailed article covering all the ins and outs.
- If you want to start incorporating DAX into SQL Server, Haroon Ashraf wrote a straightforward guide.
- Do you want to start using Power BI with SQL Server but don't know where to begin? I would recommend checking out this article by Scott Murray.
Learn more about Power BI in this 3 hour training course.
About the author
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
