In Tableau, IF is a conditional expression that returns a value when a condition is true, with optional alternatives for other conditions. Its basic pattern is IF condition THEN result ELSE other_result END; create it as a calculated field when you want to classify, filter, or calculate values based on rules.
What the IF Expression Does
Tableau’s IF expression lets a calculation choose between results. For example, it can label sales as high or low, group dates into reporting periods, or include only selected records in a measure. Tableau evaluates the condition and returns the result for the first matching branch.
Although people often call it the “IF function,” it is more precisely a conditional expression: it has a structured form rather than the function-and-parentheses form used by expressions such as ROUND(). You write it in Tableau’s calculation editor, not in a worksheet cell. The result can be used in a view like another calculated field, subject to the usual rules for dimensions, measures, and aggregation.
A simple example uses a field named [Sales] and returns a text label:
IF [Sales] >= 1000 THEN "High" ELSE "Standard" END
For each record evaluated, the expression checks whether Sales is at least 1,000. If so it returns “High”; otherwise it returns “Standard.” The threshold is an example, not a built-in Tableau rule. Replace it with a criterion suited to the data.
Syntax and Evaluation Order
The general structure supports one initial condition, any number of additional conditions, and an optional fallback:
IF condition THEN result
ELSEIF another_condition THEN another_result
ELSE fallback_result
END
IFbegins the expression, followed by a condition that evaluates to true or false.THENintroduces the value Tableau returns if that condition is true.ELSEIFadds another condition to check if the preceding condition was not true. It is written as one word.ELSEsupplies a fallback value when none of the listed conditions matches. It is optional, but including it often makes the result clearer.ENDcloses the expression.
Conditions can use comparison operators such as =, <>, >, >=, <, and <=. Use AND, OR, and NOT to combine or reverse logical tests. Text values go in straight double quotation marks, while field names are usually enclosed in square brackets.
Tableau checks branches in order. Once it finds a true condition, it returns that branch’s result and does not use later branches. Therefore, put specific or restrictive tests before broader ones. Also make sure every result branch has a compatible data type: for example, return text from every branch, or a number from every branch. Mixing a text label with a numeric value can cause a calculation error.
Practical Examples
Consider a profitability label using a field called [Profit]. This version distinguishes positive, negative, zero, and missing values:
IF ISNULL([Profit]) THEN "Missing"
ELSEIF [Profit] > 0 THEN "Profit"
ELSEIF [Profit] < 0 THEN "Loss"
ELSE "Break-even"
END
The first test matters because a missing value is not the same as zero. Without it, the final ELSE would group null Profit values with break-even records. ISNULL() checks whether a value is null and is a useful way to make missing-data behavior explicit.
An IF calculation can also create ordered bands. Suppose [Days to Ship] contains a number of days:
IF [Days to Ship] <= 2 THEN "Fast"
ELSEIF [Days to Ship] <= 5 THEN "On time"
ELSE "Delayed"
END
Because the first condition captures every value of two or fewer, the second condition effectively covers values above two through five. The ordering prevents a record with one shipping day from being evaluated as “On time.” If the bands need precise lower and upper limits, write both boundaries in each condition with AND.
To make a conditional measure, return a number for qualifying records and zero for the rest. For instance, this calculation keeps Furniture sales and contributes zero for other categories:
IF [Category] = "Furniture" THEN [Sales] ELSE 0 END
When placed in a view as a measure, Tableau can aggregate the returned values. For a metric intended to total the qualifying sales, make the aggregation explicit in the calculation:
SUM(IF [Category] = "Furniture" THEN [Sales] ELSE 0 END)
This is a common conditional-sum pattern. Another option is to return the sales value for qualifying records and NULL for others, so nonmatching records are excluded rather than represented as zero. Which version is appropriate depends on the intended meaning and how the result is later aggregated or displayed.
Conditional Logic with Aggregates
Tableau distinguishes row-level expressions from aggregate expressions. A condition such as [Category] = "Furniture" is evaluated for records, while SUM([Sales]) is an aggregate. Combining levels inconsistently can produce an error about mixing aggregate and non-aggregate arguments. Decide whether the rule should operate on each record or on an already aggregated value.
For example, the conditional sum above tests each record’s category and then sums the returned sales values. A different question—whether total sales in the current view exceed a threshold—requires an aggregate condition, such as IF SUM([Sales]) > 10000 THEN "Above target" ELSE "At or below target" END. The latter result can change with the dimensions in the view because the aggregation is evaluated at the view’s level of detail.
For conditional counts, returning 1 for a qualifying record and 0 otherwise, then summing, is a straightforward pattern: SUM(IF [Status] = "Returned" THEN 1 ELSE 0 END). This counts records that satisfy the test at the level at which the expression is evaluated. If the requirement is to count unique orders rather than records, use a distinct-count approach, such as COUNTD(IF [Status] = "Returned" THEN [Order ID] END). Check for duplicate rows and confirm that the field represents the entity you mean to count.
IF, IIF, and CASE Compared
Tableau offers other ways to express conditional logic, but they are not identical. Choose based on the kind of test and how easy the calculation will be to maintain.
- IF / ELSEIF: Best for multiple different tests, ranges, or conditions combined with logical operators. It is usually the clearest choice for thresholds and complex rules.
- CASE: Useful when comparing one expression against several specific values. For example,
CASE [Region] WHEN "East" THEN "E" WHEN "West" THEN "W" ELSE "Other" ENDmaps exact region values. CASE is less suited to ranges such as “greater than 100.” - IIF: A compact conditional expression for one test, with a true result and a false result. It also supports an optional result for an unknown test, which can be useful when null logic needs separate treatment.
Do not assume IFNULL() is a substitute for IF. IFNULL(expression, replacement) replaces a null value, while IF can test several conditions and return different results. Likewise, Tableau’s ZN() changes a numeric null to zero; that is only appropriate when zero is a meaningful replacement in the analysis.
Nulls, Data Types, and Common Errors
Nulls are a frequent source of unexpected results. A comparison involving a null value does not behave like a normal true-or-false comparison. For example, a test such as [Profit] > 0 cannot establish that a null Profit is positive. Use ISNULL([Profit]) when missing values need their own result, and place that branch before tests for positive or negative values.
Other common problems include:
- Missing END: Each IF expression must be closed with
END. Nested IF expressions need a closing END for each opening IF. - Wrong branch order: A broad condition placed first can capture values meant for a later, narrower condition.
- Incompatible results: Returning a number in one branch and a string in another can make the calculation invalid. Convert or redesign results so their types are compatible.
- Unexpected aggregation: Mixing a row-level field with an aggregate in the same expression can trigger an aggregate/non-aggregate error. Align the calculation’s level or use an appropriate aggregation.
- Zero versus null: Using zero as a fallback can affect averages, totals, and displayed marks differently from returning null. Choose based on whether nonmatching rows should count as zero or be omitted.
A calculation can be syntactically valid and still encode the wrong business rule. For example, a final ELSE "Break-even" may accidentally include missing profits if there is no explicit null branch. Test representative values from each category, including boundary values and missing data.
Create and Check a Calculated Field
In Tableau Desktop, create an IF expression through Analysis > Create Calculated Field, or right-click in the Data pane and choose Create Calculated Field. Enter a name, type the expression into the editor, and review Tableau’s validation message before selecting OK. The exact surrounding interface can vary slightly by Tableau product and version, but the calculated-field approach is the same.
For a reliable check, try this sequence:
- Write the rule in plain language, including what should happen to values not covered by the main condition.
- Translate each case into an IF or ELSEIF branch, putting more restrictive tests first.
- Check the return type in every branch and decide explicitly how nulls should be handled.
- Validate the calculation, then place it in a worksheet and inspect examples at the relevant level of detail.
- Compare totals or category counts against a small, known subset of the data where possible.
When a result appears wrong, temporarily simplify the calculation or create a table showing the input field alongside the calculated output. That makes it easier to see whether the problem is the condition, the source data, aggregation, or an unexpected null. Keep the final calculation readable: descriptive calculated-field names and line breaks in longer expressions help future workbook users understand the logic.
Frequently Asked Questions
Is IF a function in Tableau?
It is commonly called the IF function, but Tableau documents and uses it as a conditional expression. It is written with keywords such as IF, THEN, ELSE, and END rather than function parentheses.
Can Tableau IF have more than one condition?
Yes. Add multiple ELSEIF branches, or combine tests within a condition using AND, OR, and NOT. Tableau evaluates the branches in order and returns the first matching result.
What happens if I leave out ELSE?
If no condition matches and there is no ELSE branch, the result is null. Add ELSE when unmatched records need a defined label or value.
How do I test for a blank or missing value?
Use ISNULL([Field]) to test for a null value. A blank string is not necessarily the same as null, so if your data contains empty text, test or clean that case separately.
Should I use IF or CASE in Tableau?
Use IF for ranges, inequalities, and varied logical tests. Use CASE when one expression is being matched to several exact values. Both can return different results, but CASE does not replace IF for general range logic.
Related
- Microsoft Access Split Form: How It Works and When to Use It
- JavaScript String Split to Array: Examples and Tips
- Balance of Payments: How It Works and What It Shows
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula to Check If a Value Is in a ListTo check whether a value in D2 appears anywhere in the list A2:A10, use =COUNTIF($A$2:$A$10,D2)>0. Excel returns TRUE…
- Excel FILTER Function: Filter Rows by Partial TextTo return rows where a column contains part of a word or phrase, combine Excel’s FILTER function with…
- Can You Filter Out Duplicates in Excel? Methods That WorkYes. In Excel for Microsoft 365 on Windows, you can identify duplicates with conditional formatting, filter them with…
- PowerPoint Poster Slide Size: Set the Right DimensionsTo change a PowerPoint slide to poster size in Microsoft 365 for Windows, select Design > Slide Size…
- Excel Sum a Range of Cells: Formulas, Tips, and FixesTo add a range of cells in Excel, use the SUM function: if the numbers are in A1…
- Excel Formula for Time Difference: Examples and FixesTo find the time between two values in Excel, subtract the start time from the end time: with…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.