Tableau CASE function applies the syntax (CASE-WHEN) in the following order to perform logical tests.
The CASE function evaluates <expression> , compares it to a sequence of values, <Value 1>, <Value 2>, <Value 3>, <Value 4>, …etc, and returns a result. When a value that matches the expression is encountered, CASE returns the corresponding return value, <Return Value 1>, <Return Value 2>, <Return Value 3>, <Return Value 4>, …etc. If no match is found, then default return expression is used <Default Return>. If there is no default return expression and no values match, then Null is returned.
Example of a CASE-WHEN statement
Suppose you have a data set with the field named ‘Class’ and the following as members of this field (‘A’, ’B’, ‘C’, ‘D’, ‘E’, ‘F’, ‘G’ , ‘H’, ’11’ & ‘20’). Let’s say, using CASE-WHEN function you would like to assign the alphabets as follows (‘1’ to ‘A’, ‘2’ to ‘B’……. ‘8’ to ‘H’) and any other number to ‘Remain as default’ or ‘Null’.
Then CASE-WHEN would be an ideal function to perform this, and when applied in the following two ways would return the following.
When both the two syntaxes are applied in this data the result would be as per the table below;
Note: The difference between the two syntaxes is, one defines what value to assign in case there is no match found while the other does not.
Nested CASE-WHEN statement
Sometimes you can nest CASE-WHEN statement as follows to match values.
Using the above syntax in this data would result to;
CASE function is therefore powerful when you need to match values and provide a return value. A simple calculation to remember and can be used interchangeably with IIF or IF THEN ELSE functions.
However, when you need to evaluate whether a condition is TRUE or FALSE then IF function is the way to go.
I hope this article brought some light on how to work with CASE -WHEN function.
To receive more of these Tableau tips and tricks, kindly subscribe to our emailing list below.
Thank you for reading.