• Home  /

# 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```

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

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()