HomeTutorialsTableau

# Logical Functions in Tableau: IF and CASE statements

Learn about IF and CASE statements in Tableau and explore how to use these logical functions to conditionally transform and visualize data.
Mar 2023  · 7 min read

If you apply yourself, you can do anything.” “If the number is equal to 8, I win the game!” Conditional statements are part of everyday life. We use them on a regular basis to determine a course of action based on a logical evaluation:

• Do I apply myself (TRUE/FALSE)? In that case, I can do anything.
• Is the number = 8? If so, I win the game.

In Tableau, you can apply conditional logic to emulate the same logic you use on a daily basis. Tableau provides a series of logical functions that make it easy to conditionally transform the data based on a logical evaluation, either a boolean expression (TRUE/FALSE) or an exact value match. In this article, we'll explore some of the most common conditional statements in Tableau and show how you can use them to analyze and visualize data.

## Tableau IF Statements

The `IF` statement is a basic conditional statement in Tableau allowing users to evaluate a boolean expression and return a value if the condition is true, and another if it's false. The basic syntax of the `IF` statement is:

``IF [condition] THEN [value1] ELSE [value2] END``

If the final result depends on the evaluation of an alternate boolean expression, you can plug an `ELSEIF` clause into the statement like this:

``````IF [condition1] THEN [value1]

ELSEIF [condition2] THEN [value2]

ELSE [value3]

END``````

For instance, to identify pop v. non-pop music genres based on whether ‘pop’ is contained in the name, we can use the following `IF` statement:

``````IF CONTAINS([Top Genre], 'pop') THEN 'pop'

ELSE 'other genre'

END``````

### Nested IF statements

`IF` statements can also be nested to create more complex conditions and evaluate multiple conditions for subgroups of your data. To build nested `IF` statements, use the following syntax:

``````IF [condition1] THEN

IF [condition2] THEN [value1]

ELSE [value2]

END

ELSE [value3]

END``````

Let’s say that we’re interested in figuring out what proportion of pop songs are danceable (per the danceability measure defined in the Spotify Music Dataset). We can use the following nested `IF` statement to figure it out:

``````IF CONTAINS([Top Genre], 'pop') THEN

IF [Dnce] > 66 THEN 'danceable pop' // 66 is the median

ELSE 'less danceable pop'

END

ELSE 'not pop'

END``````

Nested `IF` statements are powerful functions, especially when multiple conditions need to be met for different subgroups of the data.

### IIF Statements

The `IIF` function is another conditional statement in Tableau that allows users to test a condition and return one of two values. The syntax of the `IIF` function is:

``IIF([condition], [value if true], [value if false])``

For example, to create a calculated field that identifies songwriters who produced a song that was a top hit between 2017 and 2019, we can use the following `IIF` function:

``IIF([Year] >= 2017 AND [Year] <= 2019, '2017-2019 Top Song', 'Not Top Song in 2017-2019')``

This will return “2017-2019 Top Song” if the song was a top hit between 2017 and 2019 (as measured by Billboard) and “Not Top Song in 2017-2019” otherwise.

### Using Tableau Aggregation Functions with IF

Tableau offers a wide range of built-in functions that can be used in conjunction with conditional statements to perform more advanced calculations. Some of the most commonly used aggregation functions include `COUNTD`, `SUM`, `AVG`, `MIN`, and `MAX`. With aggregate functions, you can summarize insights in an effective way by building new metrics in Tableau. You’ll need to use the following syntax:

``<Aggregation>(IF [Condition] THEN [value] END)``

For example, let's count the number of unique artists with a top hit between 2017 and 2019 with the `COUNTD` function:

``COUNTD(IF [Year] >= 2017 and [Year] <= 2019 THEN [Artist] END)``

The statement above will return the distinct number of artists who had a hit between 2017 and 2019. If an artist had multiple hits in those years, the artist's name will only be counted once.

Similarly, we can use the `SUM` and `AVG` functions in combination with a conditional statement to perform calculations on specific subsets of data.

A word of advice: be sure to wrap the condition inside of the aggregation. If you don’t, you’ll get the infamous “Cannot mix aggregate and non aggregate comparisons or results in '`IF`' expressions.” For instance, the statement below gives an error because we are trying to perform an aggregation on a single datapoint, which itself is not aggregated.

## Tableau CASE Statements

### Introduction

The `CASE` statement is another conditional statement in Tableau that allows users to perform multiple tests on a single field and return different values based on the results. Unlike `IF` statements, which rely on the evaluation of a boolean expression, `CASE` statements rely on exact value matches. If the `WHEN` clause uses boolean logic, you'll get the following error in Tableau:

The syntax of the `CASE` statement is:

``````CASE [expression]

WHEN [value1] THEN [result1]

WHEN [value2] THEN [result2]

ELSE [default result]

END``````

For example, let's rewrite the logic to identify songwriters who produced a song that was a top hit between 2017 and 2019:

``````CASE [Year]

WHEN IN (2017, 2018, 2019) THEN '2017-2019 Top Songs'

ELSE 'Not Top Song in 2017-2019'

END``````

In the case above, we use the `WHEN IN` construction to list out the years of interest (2017, 2018, and 2019). Tableau then uses the set to find an exact match in the data. If no matching data is found, and no default value is specified, a null value is returned.

### Nested CASE Statements

Similar to nested `IF` statements, you can use nested `CASE` statements in Tableau to surface complex logic using a calculated field. You can use the syntax below to return nested logic with `CASE` statements:

``````CASE [expression1]

WHEN [value1] THEN

(CASE [expression2]

WHEN [value2] THEN [result1]

WHEN [value3] THEN [result2]

…

ELSE [default result]

END)

WHEN [value4] THEN

(CASE [expression2]

WHEN [value5] THEN [result3]

WHEN [value6] THEN [result4]

…

ELSE [default result]

END)

ELSE [default result]

END``````

While `CASE` statements cannot evaluate boolean expressions and don't work to identify complex patterns in the data, they are easier to read and perform better than `IF` statements. If the data is simple enough to transform and does not need complex TRUE/FALSE evaluations, you're better off using a `CASE` statement. In some cases, you can use `IF` and `CASE` statements together for even more powerful analyses.

## Conclusion

Conditional statements, like `IF` and `CASE` statements, are effective instruments to level up your Tableau skills as a data practitioner. To go from "zero-to-hero" with Tableau, be sure to check out our resources, like the Data Analyst in Tableau career track or the Analyzing Data in Tableau course.

Once you have a good foundation, practice visualization in Tableau using the datasets available on DataLab.  `IF`, `IIF`, and `CASE WHEN` statements will be second nature to you. Good luck!

## Tableau CASE and IF FAQs

### How do I know when to use IF v CASE?.css-18x2vi3{-webkit-flex-shrink:0;-ms-flex-negative:0;flex-shrink:0;height:18px;padding-top:6px;-webkit-transform:rotate(0.5turn) translate(21%, -10%);-moz-transform:rotate(0.5turn) translate(21%, -10%);-ms-transform:rotate(0.5turn) translate(21%, -10%);transform:rotate(0.5turn) translate(21%, -10%);-webkit-transition:-webkit-transform 0.3s cubic-bezier(0.85, 0, 0.15, 1);transition:transform 0.3s cubic-bezier(0.85, 0, 0.15, 1);width:18px;}

The function you use will largely depend on the type of task you are looking to perform. Whereas an IF statement evaluates a boolean expression, a CASE statement strictly matches on a field value. Often, a CASE statement can be converted into an IF statement. The opposite is less likely, since IF statements are used to perform more complex evaluations that CASE statements aren't typically able to handle.

### What is the difference between IF and CASE?.css-167dpqb{-webkit-flex-shrink:0;-ms-flex-negative:0;flex-shrink:0;height:18px;padding-top:6px;-webkit-transform:none;-moz-transform:none;-ms-transform:none;transform:none;-webkit-transition:-webkit-transform 0.3s cubic-bezier(0.85, 0, 0.15, 1);transition:transform 0.3s cubic-bezier(0.85, 0, 0.15, 1);width:18px;}

Whereas IF statements can evaluate boolean logic on disaggregated dimensions or aggregated measures, CASE statements evaluate exact value matches only. For that reason, IF statements have more flexibility than CASE statements and can be used to perform more complex logic on the data. However, CASE statements provide greater readability and better performance than IF statements. CASE statements work great in cases where the data requires simple transformations only.

### Should I format the conditional function?

While Tableau does not offer a feature to auto-format text in the calculated field dialog (yet!), it's a good idea to add formatting for better readability, including:

• Comments: you can use // for single line comments and /* <...> */ for multi-line comments;
• Line breaks and indents: add line breaks and indents so it's easy for someone else viewing the workbook to understand the different layers of logic embedded in the visualization.

### What is the difference between IF and IIF?

The main difference between the IF statement and the IIF function is that the former can test for multiple conditions and return different values based on the results, while the latter can only test for one condition at a time and return one of two values. While you can leverage more complex logic with IF statements, the IIF function works great for simple conditions where clarity and brevity take precedence.

### Why not just use Tableau’s in-built parameter and filter features?

In some cases, the filter and parameter features won’t be enough to analyze the data. As explained in our Calculating Fields in Tableau tutorial, sometimes, you’ll need to create new variables to fill in the missing data before you can analyze it. Often, the transformations you’ll need to perform will fall into the following five categories:

• Converting the data type of a field, such as converting a string to a date or an integer to a float
• Aggregating data
• Filtering results
• Calculating ratios
Topics
Related

tutorial

Learn when and how to use conditional functions in spreadsheets.

Francisco Javier Carrera Arias

11 min

tutorial

### CASE Statements in PostgreSQL

In this tutorial, you'll learn how to write conditional queries in PostgreSQL using the PostgreSQL CASE conditional expression.

Sayak Paul

7 min

tutorial

Learn how to utilize conditional formatting with one or multiple conditions and how to color cells by custom criteria with the help of a dataset.

11 min

tutorial

### Conditionals and Control Flow in R Tutorial

Learn about relational operators for comparing R objects and logical operators for combining boolean TRUE and FALSE values. You'll also construct conditional statements.

13 min

tutorial

### Data Visualization with Tableau

In this tutorial, you will learn how to analyze and display data using Tableau and make better, more data-driven decisions.

Parul Pandey

31 min

tutorial