Using CASE Statements in Tableau (A How-To and Comparison with SQL)

What is a CASE Statement in Tableau?

A calculated field in Tableau is a custom field you create using existing data in your dataset. It allows you to perform calculations, transformations, and create new data points using formulas. Calculated fields are powerful tools that let you manipulate and analyze your data without modifying the underlying data source.

One common type of calculation you can perform in a calculated field is a CASE statement. This is a logical function used to perform multi-way condition checks on a single expression. It evaluates an expression against a list of values, returning a result when a match is found. Think of it like a switch-case in programming: you specify WHEN value THEN result options (and optionally an ELSE default) to handle different values.

For example:

CASE [Season]
    WHEN 'Summer' THEN 'Sandals'
    WHEN 'Winter' THEN 'Boots'
    ELSE 'Sneakers'
END

Tableau CASE vs. SQL CASE WHEN

Key differences between Tableau and SQL CASE statements:

• Simple CASE Only: Tableau’s CASE works like SQL’s simple CASE, checking one expression against specific values.

• No Boolean Conditions: Unlike SQL, Tableau’s CASE cannot evaluate arbitrary boolean expressions (e.g., WHEN [Profit] > 0). For these scenarios, use IF/THEN/ELSEIF instead.

• Exact Matches Only: Tableau CASE requires exact value matches, though you can use WHEN IN for multiple values.

Syntax and Examples

Categorizing values

Use a CASE statement to categorize values.

CASE [Region]
    WHEN 'North' THEN 'Cold Climate'
    WHEN 'South' THEN 'Warm Climate'
    WHEN 'East' THEN 'Coastal'
    WHEN 'West' THEN 'Mountains'
    ELSE 'Unknown'
END

Parameter-based measure selection

Use a CASE statement to select a measure based on a parameter.

CASE [Choose a Measure]
    WHEN "Sales" THEN SUM([Sales])
    WHEN "Profit" THEN AVG([Profit])
    WHEN "Quantity" THEN COUNT([Quantity])
    ELSE 0
END

Multiple value matching using IN

Use a CASE statement to match multiple values.

CASE [Payment Method]
    WHEN IN ('Visa', 'Mastercard', 'Amex') THEN 'Credit Card'
    WHEN IN ('PayPal', 'Apple Pay') THEN 'Digital Payment'
    WHEN 'Cash' THEN 'Cash'
    ELSE 'Other'
END

Creating size categories

Use a CASE statement to create size categories.

CASE [Size]
    WHEN 'XS' THEN 1
    WHEN 'S' THEN 2
    WHEN 'M' THEN 3
    WHEN 'L' THEN 4
    WHEN 'XL' THEN 5
    ELSE 0
END

Common Use Cases

• Re-aliasing Values: Simplifying categories or standardizing labels. Example: WHEN 'Electronics & Computers' THEN 'Electronics' to standardize product categories
• Creating Ordinal Buckets: Assigning discrete values to bands/tiers. Example: WHEN IN (0, 1000) THEN 'Low Value' to create value segments
• Custom Sorting: Defining non-standard sort orders. Example: WHEN 'Critical' THEN 1 to assign custom sort priorities
• Dynamic Field Selection: Using parameters for measure/dimension switching. Example: WHEN 'Revenue' THEN SUM([Sales]) to switch between different metrics
• Null Handling: Replacing missing values with defaults. Example: WHEN NULL THEN 'Not Specified' to handle missing data

Best Practices

1. Use CASE for single-field, multi-value logic
2. Use IF statements for complex conditions involving ranges or multiple fields
3. Always include an ELSE clause for unexpected values
4. Format for readability with proper line breaks and indentation
5. Consider Groups or data modeling for very large mappings
6. Test thoroughly, especially for case sensitivity and edge cases

SQL-Native Approach in Evidence

Evidence uses standard SQL for conditional logic, offering:

• Full SQL CASE WHEN functionality with boolean expressions
• Database-side execution for better performance
• Centralized logic in the data layer
• Native SQL syntax without proprietary formulas
• Version control and collaboration through code
• Integration with data engineering workflows

Example in Evidence:

Categorizing values

SELECT 
CASE region
    WHEN 'North' THEN 'Cold Climate'
    WHEN 'South' THEN 'Warm Climate'
    WHEN 'East' THEN 'Coastal'
    WHEN 'West' THEN 'Mountains'
    ELSE 'Unknown'
END AS climate_zone
FROM your_table

Multiple value matching

SELECT 
CASE 
    WHEN payment_method IN ('Visa', 'Mastercard', 'Amex') THEN 'Credit Card'
    WHEN payment_method IN ('PayPal', 'Apple Pay') THEN 'Digital Payment'
    WHEN payment_method = 'Cash' THEN 'Cash'
    ELSE 'Other'
END AS payment_category
FROM your_table

Creating size categories

SELECT 
CASE size
    WHEN 'XS' THEN 1
    WHEN 'S' THEN 2
    WHEN 'M' THEN 3
    WHEN 'L' THEN 4
    WHEN 'XL' THEN 5
    ELSE 0
END AS size_order
FROM your_table

This SQL-first approach leverages database optimization and maintains consistency across reports.