Search
Clear search
Close search
Google apps
Main menu

About CASE

Return dimensions and metrics based on conditional expressions.

CASE statements let you create new fields that use conditional logic to determine the field values. CASE is most often used 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 statement like this:

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

You must be able to edit a data source to create CASE statements.

In this article:

Create a CASE statement

To create a CASE statement:

  1. Edit your data source
  2. Click + to create a new calculated field
  3. Enter a name for the new field
  4. In the formula field, enter the CASE statement.

CASE Syntax

CASE
    WHEN condition THEN result
    WHEN condition THEN result
    WHEN condition THEN result
    ... 
    ELSE result 
END

Parameters:

  1. condition—an expression which evaluates to a boolean value (true or false). Conditions can include dimensions or metrics, but not both.
  2. result—the value to return. Can be a dimension, metric, or literal value.
  3. ELSE result—(optional) the default value to return if no other condition is met.

A CASE statement consists of one or more logical conditions (WHEN clause), followed by a result to return if that condition is true (THEN clause). If no conditions are true, CASE returns the value in the ELSE clause.

WHEN conditions

A WHEN condition evaluates your data and returns true if the specified condition is met, or false if it isn't. Valid WHEN conditions can do any of the following:

  • Compare a dimension or metric to a literal value, using a supported operator
  • Perform a regular expression match on a dimension or metric
  • Determine whether a dimension or metric is null
  • Determine whether a dimension or metric contains a value specified in a list
  • Determine whether a dimension or metric does not contain a value specified in a list

Notes

You can't mix dimensions and metrics in WHEN conditions. For example, the following is not allowed:

CASE
    WHEN Favorite Color = "red" AND Secret Number = 42 THEN ... // error!

If you need to return a metric based on the value of a dimension, consider using a conditional calculated field. The basic technique is to embed the CASE statement within a calculated field. For example,

SUM (CASE WHEN Favorite Color = "red" THEN 1 ELSE 0 END) // count number of red lovers

Learn more about conditional calculated fields.

WHEN conditions must contain either a dimension or a metric. You can't have a literal-only condition. For example, the following is not allowed:

CASE
    WHEN 1 THEN ... // error!

It's not possible to do math or use calculated field functions in a WHEN condition. For example, the following is not allowed:

CASE
    WHEN metric1 + metric2 > 4 THEN ... // error!

You can work around this by first creating a calculated field with the desired formula. Then use that field in the CASE statement. For example,

1) Create a calculated field, My field, with the following formula:

metric1 + metric2

2) Use My Field in CASE:

CASE
    WHEN My field > 4 THEN ...

THEN results

Each WHEN condition must have a matching THEN clause, which specifies the value to return if that condition is true. If there are multiple WHEN conditions, the result for the first condition that is true is returned.

All of the possible results in a CASE statement must be of the same type. For example, if the first THEN clause returns text, additional THEN clauses and the ELSE clause must also return text.

ELSE results

The ELSE clause allows you to specify a default result. This will be returned if none of the WHEN clauses are true. A CASE statement can only have 1 ELSE clause.

Comparison operators

You can use the following operators in WHEN clauses:

  • =
  • !=
  • >
  • >=
  • <
  • <=

The operands on both sides of the comparison operators must of the same type: text, number or boolean. At least one operand must be a field, the other one can be a text, number, or boolean.

Examples

CASE WHEN Name = 'tony' THEN 'family' ELSE 'friend' END

CASE WHEN Age > 18 THEN "adult" ELSE "minor" END

SUM (CASE WHEN Duration<=90 THEN 1 ELSE 0 END)

Logical operators

You can add AND and OR conditions to WHEN conditions. Use parentheses to group conditions and set order of precedence.

Examples

CASE WHEN (Beer Type = "IPA" OR IBU > 40) THEN "Too bitter" ELSE "Yum!" END

CASE 
   WHEN (REGEXP_MATCH(Dimension1, ".*foo") 
        AND Dimension2 = "bar" ) 
        OR Dimension3 IS NULL 
   THEN "Yes!" 
   ELSE "Huh?" 
END

Functions in CASE statements

You can use the functions below in WHEN conditions.

Why can't I use other functions in CASE?

WHEN conditions must be a boolean expression. Except for REGEXP_MATCH, calculated field functions return non-boolean results (i.e., numbers, text, dates, geo codes).

IS NULL

Returns true if the input field is empty or null.

This function is only available inside CASE. You can't use IS NULL in standalone calculated fields.

Syntax

field IS NULL

Parameters:

field—the dimension or metric to evaluate.

Examples

Field contains Formula Results
a Field IS NULL false
  Field IS NULL true

IN

Returns true if the input field contains any of the the listed values. Both the field and list values must be of the same type. IN matches complete values and is case-sensitive. For partial value or case insensitive matching, use REGEXP_MATCH.

This function is only available inside CASE. You can't use IN in standalone calculated fields.

Syntax

field IN (Value1, Value2, ... ValueN)

Parameters:

field—the dimension or metric to evaluate.

Examples

Field contains Field type Formula Results
Bob Text Field IN ('Bob', 'Alice') true
Alice Text Field IN ('Bob', 'Alice') true
Jorge Text Field IN ('Bob', 'Alice') false
20 Number Field IN (10, 20) true
10 Number Field IN (10, 20) true
100 Number Field IN (10, 20) false
Bob Text Field IN ('Bob', 20) error (because Field is text but 20 is a number. To fix this, put "20" in quotes.)

NOT IN

Returns true if the input field does not contain one of the listed values. Both the field and list values must be of the same type. IN matches complete values and is case-sensitive. For partial value or case-insensitive matching, use REGEXP_MATCH.

This function is only available inside CASE. You can't use NOT IN in standalone calculated fields.

Syntax

field NOT IN (value1, value2, ...valueN)

Parameters:

field—the dimension or metric to evaluate.
value1,value2,...valueN—the values to look for.

Examples

Field contains Field type Formula Results
Jorge Text Field NOT IN ('Bob', 'Alice') true
Alice Text Field NOT IN ('Bob', 'Alicia','Hiroshi') true
Bob Text Field NOT IN ('Bob', 'Alice') false
20 Number Field NOT IN (10, 20) false
10 Number Field NOT IN (10, 20) false
100 Number Field NOT IN (10, 20) true
Bob Text Field NOT IN ('Bob', 20) error*

* Because Field's type is text but 20 is a number. This would work if "20" was delimited by quotes.

REGEXP_MATCH

Returns true if the input field matches the regular expression. This function is not limited to CASE statements and can be used in other types of calculated fields.

Learn more about REGEXP_MATCH

Was this article helpful?
How can we improve it?
What's new in Data Studio

Learn about new features and recent changes.