6.2. The CASE Function in Looker Studio

Let's dive into a more advanced function for categorizing data: the CASE function.

The CASE Function

Imagine working with a client that only operates in certain US states. They don't expect any conversions from non-serviceable states. In this scenario, you'd want to categorize all states into either serviceable or non-serviceable. The CASE function is perfect for this.

Let's consider a similar example with countries. You might have an e-commerce store that only ships to certain countries. You can categorize your traffic into serviceable or non-serviceable countries using the CASE function.

The basic structure of the CASE function is as follows:

CASE
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  ...
  ELSE default_result
END

The CASE function starts with the CASE keyword and ends with the END keyword. Between these keywords, you can have as many WHEN and THEN clauses as you want. Each WHEN clause has a condition, which could be any expression that returns true or false. If the condition is true, the value after the corresponding THEN clause will be returned.

Here's an example of using the CASE function:

CASE
  WHEN Country IN ('United States', 'Canada') THEN 'Serviceable'
  ELSE 'Non-serviceable'
END

In this example, if the country is in the United States or Canada, the function returns "Serviceable." Otherwise, it returns "Non-serviceable."

Keep in mind that the CASE function can only be used with WHEN, not with IF. The IF example was only used to help illustrate how the function works.

Some practical tips for formulas in Looker Studio will be discussed in future lessons.

The IN Operator

The CASE function example above introduced the IN operator, which is useful when you want to check if a value is equal to any of the values listed within the parentheses. It returns true if the value of the dimension matches any of the values listed in the IN operator.

For example:

Country IN ('United States', 'Canada')

This expression returns true if the country is either the United States or Canada.

Keep in mind that like IF() function the IN operator in Looker Studio is case-sensitive.