• Home  / 
  • Tableau Case Function – When Should You Use it?

Tableau Case Function – When Should You Use it?

 CASE and Nested Case Functions in Tableau

Summary:

“CASE-WHEN” is the part of Logical functions in Tableau. “CASE-WHEN” are used to perform the logical test and return the required value when the test expression is true. Check out Tableau For Data Science Course for more tips and tricks or 40 Awesome Tableau Keyboard Shortcuts


How CASE-WHEN works:

It starts with “CASE” function evaluating the 1st logical expression corresponding to set/sequence of values and when the logical expression becomes True, it returns the respective specified value as result. If in logical expression no match is found then the value of default return expression is used. In case no default value is being mentioned by the user then “NULL” is being returned.

 

Syntax of CASE-WHEN:

This function finds the first <value> that matches the given <expression> and returns the corresponding <return value>

Example:

CASE [<expression>]

WHEN <value1> THEN <return value1>

WHEN <value2> THEN <return value2>

ELSE <return value3>

END

 

Advantages of using CASE-WHEN:

1. CASE-WHEN statements are easier to write and comprehend.

2. Because of its simplicity, for a user, it is helpful to avoid making mistakes like referencing the wrong field

3. CASE-WHEN statements perform faster than IF-ELSE statements

 

Disadvantages of using CASE-WHEN:

1. Usage of CASE-WHEN in Tableau is very limited as they cannot perform boolean algebra conditions

2. CASE-WHEN in tableau only compares the expression to the exact values. Conditional operators like “OR”, “AND” can’t be used with CASE-WHEN

3. Using CASE-WHEN, multiple expressions can’t be evaluated in a single line

 

Examples of CASE-WHEN:

Simple CASE-WHEN Statement:

Let’s say there is a field named as “Grade” in the dataset. The values in the “Grade” field are “A”, “B”, “C” and “D”. Now, suppose the requirement is to assign the weighted number(“5” for grade “A”, “4” for grade “B” etc.) based on the grades. Using CASE-WHEN statement this can be done easily in Tableau. Following is the syntax of this:

CASE [Grade]

WHEN “A” THEN 5

WHEN “B” THEN 4

WHEN “C” THEN 3

WHEN “D” THEN 2

ELSE 1

END

In the above syntax, if there is no grade mentioned in that case default value 1 is get assigned.

 

Nested CASE-WHEN Statement:

Let’s consider the below dataset:

Region Country Sales
Asia India
Asia China
Europe France
Europe Italy
Europe Spain
USA USA

Now the requirement is to allocate the sales values based on the regions and countries. To do that directly in tableau, we can use nested CASE-WHEN statement as shown below:

CASE [Region]

WHEN “Asia” THEN 

                (CASE [Country] 

                WHEN “India” THEN 2000

                WHEN “China” THEN 2500

                END)

WHEN “Europe” THEN

                (CASE [Country] 

                WHEN “France” THEN 3000

                WHEN “Italy” THEN 2200

                WHEN “Spain” THEN 1575

                END)

WHEN “USA” THEN

                (CASE [Country] 

                WHEN “USA” THEN 3500

                END)

END

 

Here is the resulting Data

Region Country Sales
Asia India  2000
Asia China  2500
Europe France  3000
Europe Italy  2200
Europe Spain  1575
USA USA  3500

 

How is CASE Different From IF or IFF

There are some fundamental differences between these functions. For the most part IF and CASE can be used interchangeably to match values and provide a return value.  However, the difference here is that CASE statements cannot return a result based on a true-false statement otherwise known as a boolean. There IF is evaluating whether your condition is TRUE or FALSE  while CASE is evaluating whether a value matches the value in the case expression. IIF statement is a simplified version of IF. It simply provides an answer when true, an answer when false, an answer when not false or true.  This is equivalent to using an IF with ELSEIF and ELSE.

Examples:

If you wanted to return “Rich” for customers with greater than 1,000,000 in their savings account, CASE will not work because it only will match the value.   Then is a true or false statement. Not a matching statement.

IF and IFF

IF [SAVINGS] > 0 THEN ‘Green’  ELSE ‘Red’ END

This will produce NULLs in your data because nothing happens when  the value is 0

IF [SAVINGS] > 0 THEN ‘Green’ ELSEIF [SAVINGS]=0   THEN ‘Yellow’ ELSE ‘Red’ END

IFF [SAVINGS] >0 ,’Green’, ‘Red’, ‘Yellow’

IFF just gives you a quick and easy way to provide an answer for simple logical true or false. The third alternative is a wall to fill in NULLs.

A CASE function cannot work in the above example because there are logical true or false statements not when a condition is present.

When to use CASE, IF and IFF

  • When simply matching values with values use CASE()
  • For simple true and false(boolean) statements use IIF()
  • For complex logic that requires calculations or multiple conditions use IF()