6.4. CASE Functions with Multiple WHEN Clauses

Let's examine a practical use case for the CASE function with multiple WHEN clauses.

Assume you're working with a data source that doesn't group countries into continents, and you want to report on continents instead of individual countries. You can use the CASE function to define continents as groups of countries like this:

CASE
  WHEN Country IN ('United States', 'Canada') THEN 'Americas'
  WHEN Country IN ('Germany', 'France', 'United Kingdom', 'Spain') THEN 'Europe'
  WHEN Country IN ('India', 'China', 'Japan') THEN 'Asia'
  ELSE 'Not grouped'
END

Here, we have three different WHEN clauses to classify countries into continents (Americas, Europe, and Asia). You can expand this list as needed.

The CASE function's result can be used for content grouping, page grouping, channel grouping, or any other grouping based on your business logic.

When creating a CASE function, it's a good idea to start with the dimension in the table to verify that the function is working correctly. This allows you to check the result row by row, as the CASE function works at the row level. However, once the function is working as expected, you can remove the original dimension from the table.

In this chapter:

→ 6.5. Tips for Editing Formulas in Looker Studio

→ 6.6. Using CASE Function for Cleaning Data

→ 6.7. Order of WHEN Clauses in CASE Function

→ 6.8. Returning Expressions in CASE Functions

→ 6.9. Using CASE to Extract URL Query Parameters

→ 6.10. Mixing Dimension & Metrics in CASE

→ 6.11. CASE Function: Aggregating Results

→ 6.12. Misc Functions: IMAGE() & HYPERLINK()

Enjoying the free version of Looker Studio Masterclass 2022?

Join the new & updated Looker Studio Masterclass Live 2024 →

📩 Receive my weekly Looker Studio tips

🎥  Subscribe to my YouTube channel

🖇 Connect with me on LinkedIn