Logical functions in Tableau evaluate conditions and return results such as a label, number, or Boolean value (TRUE or FALSE). The main tools are IF, IIF, and CASE for branching, AND/OR/NOT for combining conditions, and null-handling functions such as ISNULL, IFNULL, and ZN.
What Tableau means by logical functions
A logical calculation checks whether a statement is true, false, or sometimes unknown because a value is null. Tableau uses these tests to classify records, apply business rules, filter data, and decide which calculation to return. For example, a calculated field can label a sale as “High” when its amount exceeds a threshold, or return a different result for each region.
Some related Tableau features are often grouped together in conversation, although they do different jobs. IF, IIF, and CASE choose among outcomes; AND, OR, and NOT combine or reverse Boolean tests; and ISNULL, IFNULL, ISNOTNULL, and ZN address missing values. The result of a logical test is usually Boolean, while a conditional expression can return another data type, provided its possible return values are compatible.
To create a calculation in Tableau Desktop, use Analysis > Create Calculated Field, give the field a name, and enter an expression in the calculation editor. You can then use that field in a view, another calculation, a filter, or a set of labels, subject to the rules of the view and the data source.
How IF expressions work
IF is the most flexible choice when the conditions involve comparisons, ranges, or multiple branches. Its general structure is:
IF condition THEN result ELSE other_result END
For example, if a data source has a numeric field named [Sales], this calculated field separates records into two groups:
IF [Sales] >= 1000 THEN "Large sale" ELSE "Other sale" END
Tableau tests the condition and returns the result after THEN when it is true. Otherwise, it proceeds to ELSE, if one is present. A calculation can include multiple branches with ELSEIF:
IF [Sales] >= 1000 THEN "Large" ELSEIF [Sales] >= 500 THEN "Medium" ELSE "Small" END
The order matters: Tableau uses the first true branch, so put the most restrictive or highest-priority condition first. Here, a sale of 1,200 is classified as Large, not Medium. If ELSE is omitted and no condition matches, the result is generally null. Adding an explicit ELSE makes the fallback visible and can prevent unexpected blanks.
IIF, CASE, and choosing a branching pattern
IIF is a compact function for a single condition with a true and false result. Its basic form is IIF(test, then, else). For the same sales classification with two outcomes, it can be written as:
IIF([Sales] >= 1000, "Large sale", "Other sale")
IIF also accepts an optional fourth argument for a test that evaluates to unknown, which can occur when the test involves a null. For example, IIF([Sales] >= 1000, "Large", "Other", "Missing sales") makes the unknown case explicit. This is useful when missing data should not be treated as an ordinary false result.
CASE is convenient when one expression is compared with a list of specific values. For example:
CASE [Region] WHEN "East" THEN "Eastern" WHEN "West" THEN "Western" ELSE "Other" END
CASE is generally clearer than a long chain of equality tests when each branch matches one value of the same field. It is not the right structure for ranges such as sales above or below a threshold; use IF for those comparisons. Also, CASE comparisons do not replace an explicit null check. If [Region] may be null, test it with ISNULL in an IF expression rather than expecting a WHEN null branch to match.
- Use IF for ranges, several different conditions, or priority rules.
- Use IIF for one concise true/false decision, optionally with an unknown result.
- Use CASE when one field is matched against several known values.
Combining conditions with AND, OR, and NOT
AND requires every connected test to be true. OR requires at least one test to be true. NOT reverses a Boolean result. These operators are useful inside IF conditions and filters. For example, a calculation could identify orders that are both profitable and large:
IF [Profit] > 0 AND [Sales] >= 1000 THEN "Priority" ELSE "Standard" END
To include records from either of two regions, use OR:
[Region] = "East" OR [Region] = "West"
When an expression mixes AND and OR, use parentheses to show the intended grouping. Tableau evaluates NOT before AND, and AND before OR; parentheses make complicated logic easier to inspect and reduce the risk of a condition being read differently than intended. For example, if either region should qualify only when sales meet a threshold, write:
([Region] = "East" OR [Region] = "West") AND [Sales] >= 1000
Without those parentheses, the condition could apply the sales requirement to only one side of the OR, depending on the expression’s structure. A practical habit is to group each business rule explicitly, then test boundary values and representative records.
Nulls and missing data
A null is not the same as zero, an empty string, or the text “Null.” It represents an absent or unknown value. Comparisons involving null can yield an unknown result instead of TRUE or FALSE, which is why a test such as [Discount] = NULL is not the proper way to find missing discounts. Use ISNULL([Discount]), which returns TRUE when the field is null, or ISNOTNULL([Discount]) for the opposite test.
IFNULL replaces a null with a specified alternative. With [Discount] as a numeric field, IFNULL([Discount], 0) returns zero when the discount is missing and otherwise returns the original value. This can be useful in arithmetic, but it changes the meaning of the data: a missing discount is not necessarily a confirmed zero discount. Choose a replacement only when it is appropriate for the business definition.
ZN is a numeric null-handling function: ZN([Profit]) returns zero if [Profit] is null and otherwise returns the numeric value. It is a concise option for numeric calculations, while IFNULL can be used with compatible result types more generally. Neither function repairs the source data; they only change how a calculation treats nulls.
For example, a label that distinguishes missing sales from ordinary low sales can be written as:
IF ISNULL([Sales]) THEN "Missing" ELSEIF [Sales] < 500 THEN "Below 500" ELSE "500 or more" END
This avoids collapsing missing values into the below-500 group. That distinction matters in reports where blanks indicate incomplete collection, not actual zero activity.
Where logical calculations are evaluated
A Tableau calculation may be evaluated using data from the underlying source or within Tableau’s query and view process. The exact behavior can depend on whether fields are dimensions or measures, the level of detail in the view, the data source, and whether a calculation is aggregate or row-level. Logical functions do not remove those distinctions.
For instance, a row-level expression such as IF [Profit] > 0 THEN "Profitable" ELSE "Not profitable" END classifies individual records. If a calculation instead refers to an aggregate such as SUM([Profit]), its result is based on the aggregation level of the view. Mixing aggregate and non-aggregate expressions in one calculation can trigger a Tableau error; make the expressions consistent with the calculation’s intended level.
Logical expressions can also be used in filters. A Boolean calculated field can be dragged to the Filters shelf and set to retain TRUE, for example. This can keep a complex rule reusable and make it easier to give the field a descriptive name. Keep in mind that a filter changes which data appears in the view; it does not necessarily change the underlying source records.
Common errors and ways to troubleshoot
- Missing END: IF and CASE expressions need an END. Check that each branch is closed and that parentheses are balanced.
- Incompatible result types: Return values in the branches should have compatible types. Returning a number from one branch and a text label from another can cause a calculation error. Convert or redesign the results consistently.
- Null comparisons: Replace checks such as [Field] = NULL with ISNULL([Field]). Decide explicitly whether an unknown condition should remain null or receive a fallback.
- Incorrect branch order: In an IF/ELSEIF chain, the first true condition wins. Move narrow or high-priority conditions before broader ones.
- Unexpected AND/OR results: Add parentheses around grouped conditions, then verify the expression against records that should pass and fail.
- Aggregate mismatch: If Tableau reports that aggregate and non-aggregate arguments cannot be mixed, review whether every field in the expression should be aggregated or whether the calculation should stay row-level.
When debugging, temporarily create a simpler calculation that returns the condition itself, or create separate Boolean fields for each part of a complex rule. Place them in a text table with the relevant source fields. This makes it easier to see whether the issue comes from the test, a null, branch order, or aggregation.
Practical design guidance
A readable calculation is easier to maintain than a clever but compressed one. Give calculated fields names that describe their meaning, such as “Sales band” or “Has valid discount,” and use clear fallback outcomes. Avoid repeating the same long condition in several branches if a simpler intermediate field can express it once.
Before relying on a logical calculation in a dashboard, check the cases most likely to be missed:
- Values exactly at a threshold, such as 500 when the test is greater than or equal to 500.
- Null values and, where relevant, zero or blank text values.
- Records that satisfy more than one condition, to confirm branch priority.
- Different levels of detail in the view, especially when the calculation uses aggregates.
Also consider whether the rule belongs in Tableau or in the data source. A Tableau calculated field is useful for analysis-specific logic and quick iteration. A shared business rule used by several reports may be better defined centrally in a governed data source or upstream transformation, so different workbooks do not implement slightly different versions.
Frequently Asked Questions
What are the logical functions in Tableau?
Common logical tools include IF, IIF, CASE, AND, OR, NOT, ISNULL, ISNOTNULL, IFNULL, and ZN. They support conditional decisions, Boolean tests, and null handling.
What is the difference between IF and IIF in Tableau?
IF supports multiple branches with ELSEIF and is suited to complex rules. IIF is a compact function for one test and its true and false results, with an optional result for an unknown test.
When should I use CASE instead of IF?
Use CASE to map one expression, such as [Region], to outcomes for specific values. Use IF when conditions involve ranges, multiple fields, or different comparisons.
How do I check for a null value in Tableau?
Use ISNULL([Field]) to test whether a field is null, or ISNOTNULL([Field]) to test that it is not. Do not use an equality comparison with NULL.
Why does a Tableau logical calculation return null?
A condition may evaluate to unknown because it involves a null, or an IF expression may have no ELSE branch and no condition may match. Check the inputs and add an explicit null test or fallback if that matches the intended rule.
Related
- Bulls Eye 1-2-3 Data Sheet: Specs, Uses and Safety
- Y Balance Score: How to Calculate and Interpret It
- D&A on the Balance Sheet: Where It Appears and Why
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- How to Filter by Color in ExcelTo filter by color in Excel for Microsoft 365 on Windows, turn on filters for your data, open…
- Excel Split Cell Function: Formulas, Tools, and TipsExcel does not have one universal “Split Cell” function, but you can separate a cell’s contents with Data…
- Excel INDEX Function Examples: Useful Formulas and UsesExcel’s INDEX function returns a value—or a reference to a value—from a range based on its row and…
- Excel Split Function: How TEXTSPLIT Returns an ArrayExcel does not have a worksheet function named SPLIT; in Microsoft 365 for Windows, use TEXTSPLIT to divide…
- Excel Power Query Split Function: How to Use ItThe Excel Power Query split function is the Split Column command in the Power Query Editor, which divides…
- Insert a Checkbox in Excel Using a Formula: Step-by-StepTo show a formula-driven checkbox in Excel, put a TRUE or FALSE value in A2 and, with that…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.