CASE

Evaluates a list of conditions and returns the first matching result expression.

CASE defines one or more conditions and returns a result when a condition is met. You can define a default result if none of the conditions is met.

In this article:

Example

A common use for CASE is to create new categories or groupings of data. For example, to group selected country values into a Sales Region dimension, you might create a CASE expression like this:

CASE 
    WHEN Country IN ("USA","Canada","Mexico") THEN "North America" 
    WHEN Country IN ("England","France") THEN "Europe" 
    ELSE "Other" 
END

How searched CASE works

A CASE expression begins with the CASE keyword and ends with the END keyword. In between, you'll have a number sections or "clauses":

  • WHEN: a condition you want to evaluate. You can have multiple WHEN clauses in a single CASE expression.
  • THEN: the result to return if the WHEN clause's condition is true. You must have one THEN clause for each WHEN clause in your CASE expression.
  • ELSE. Optional. If none of the WHEN clause conditions are true, CASE returns the value in the ELSE clause, or NULL if no ELSE clause is specified.

CASE evaluates each successive WHEN clause and returns the first result where the condition is true. Any remaining WHEN clauses and the ELSE result are not evaluated. If all WHEN conditions are false or NULL, CASE returns the ELSE result, or if no ELSE clause is present, returns NULL.

Searched CASE syntax


CASE
    WHEN condition THEN result
    [WHEN condition THEN result]
    [...]
    [ELSE else_result]
END

Parameters

WHEN condition

WHEN conditions evaluate your data and return true if the specified condition is met, or false if it isn't. You can use any valid Boolean expression as the WHEN conditions.

THEN result

Each WHEN condition must have a matching THEN clause, which specifies the results if that condition is true. If there are multiple WHEN conditions, the CASE expression returns the result for the first true condition.

Notes

All of the possible results in a THEN clause must be of the same type.

For example, if the first THEN clause returns Text, additional THEN clauses, as well as the ELSE clause must also return Text.

ELSE else_result

The optional ELSE clause specifies a default result. This is returned if none of the WHEN clauses are true. If a CASE expression has no ELSE clause, and none of the WHEN clauses are true, the statement returns NULL. A CASE expression can only have 1 ELSE clause.

More examples

Check for inequality

CASE WHEN Medium != "cpc" THEN "free" ELSE "paid" END
For simple "if/then/else" scenarios, consider using the IF function.

Classify numeric dimension values into discrete buckets

For example, you can separate orders into Small, Medium, Large based on order amount:

CASE
    WHEN Amount < 20 THEN "Small"
    WHEN Amount >= 20 and Amount < 100 THEN "Medium"
    WHEN Amount >= 100 THEN "Large"
END

Evaluate a logical AND condition

CASE 
    WHEN Country ISO Code = "US" AND Medium = "cpc" 
    THEN "US - Paid" 
    ELSE "other" 
END

Evaluate a logical AND/OR condition

CASE 
    WHEN REGEXP_MATCH(Video Title, ".*Google Analytics*")
    AND is_livestream = TRUE 
    OR Video Length > 600 
    THEN "GA Livestream or long video" 
END

Evaluate a parameter

For example, you can set a "Sales Target" parameter and adjust it to see which orders met the target:

CASE WHEN Qty Sold >= Sales target parameter THEN 1 ELSE 0 END


Return a different field or value depending on a parameter value

The first example returns a dimension based on the parameter value selected. You can use this technique to let users switch the breakdown dimension used by a chart.

The second example shows how to return a different metric based on a parameter value.

CASE 
    WHEN Breakdown = "Product Category" THEN Product Category 
    WHEN Breakdown = "Store" THEN Store 
END

Note: you can also write this using simple CASE syntax:

CASE Breakdown 
    WHEN "Product Category" THEN Product Category
    WHEN "Store" THEN Store
END

CASE WHEN Show Adjusted Cost = TRUE THEN Cost + Adjustments ELSE Cost END

Nested CASE statements

You can nest CASE statements to create more complex "branching" logic:

CASE WHEN REGEXP_CONTAINS(SKU, "Hats") THEN
   CASE WHEN Color = "blue") THEN "BLUE HAT" ELSE "JUST A HAT" END 
ELSE 
  "NOT A HAT"
END
Was this helpful?
How can we improve it?
true
Get Looker Studio Pro

Upgrade now to get all the features of Looker Studio that you already know, plus enhanced enterprise capabilities and technical support

Search
Clear search
Close search
Google apps
Main menu
7829072459100154282
true
Search Help Center
true
true
true
true
true
102097