CASE, IIF and IF…which is which?

by Agnieszka Atlasik

These 3 logical functions seem somewhat similar and can sometimes get mistaken for one another. So by applying a different view to help remember which one is which, lets head to the kitchen to get a little taste of how each one will compliment your dish.

CASE function or the marinade of Tableau:

CASE statements are handy for adding “flavor” to one field at a time, as you would with a marinade.

As with marinades, they may be complex and add some layers to a “field” to enhance it.

This is done by the matching of values creating an output of a pre-defined result.

A typical example of a CASE function may be in connecting a string parameter to values.

IIF function or the sauce of Tableau:

IIF functions are the inbetweeners of CASE and IF statements. In other words, an abridged version of the IF function. They are Boolean friendly and are generally used to validate if a condition is met.

Apart from being quite intuitive, IIF functions are great label makers. They assist in drawing the eye to trends.

To apply this to an example, a Boolean can be easily improved by replacing it with an IIF function that will:

  1. Colour bars that have reached a target
  2. Customize labels in the legend to describe ‘TRUE’ or ‘FALSE’ results
  3. Have an added benefit of a third option (after TRUE and FALSE) for when the result is unknown or NULL
IF functions or the condiment of Tableau:

Lastly, the salt and pepper that is challenging to do without, an IF function. It adds complexity and multiple combinations of expressions/conditions to a visualization. Apart from having the same abilities as CASE and IIF functions, the advantage comes in with a capacity to hold series of expressions with the help of an ELSEIF.

An example of multiple conditions is the colouring of each quadrant below.

Although all the above functions can transform dimension fields to measure values and vice versa, they each have their best case uses.

In short, without food, a summary from the above:

  • CASE functions are commonly referring to one field at a time, are limited due to the lack of Boolean functionality and can be replaced with an IF function.
  • IIF functions are simplified, easy-to-use IF functions.
  • And last but not least, IF functions are incredibly versatile, have the same abilities as CASE and IIF functions, and contain added functionality of evaluating a series of expressions.

© 2022 The Information Lab Ltd. All rights reserved.