6.10. Mixing Dimension & Metrics in CASE Function

Let’s see how we can use both metric and dimension fields in one CASE function in Looker Studio.

In particular, we will use a dimension value and return a series of numbers using a simple CASE formula.

With the updated CASE function in Data Studio, you can now mix dimensions and metrics in a single CASE function, similar to how it works in SQL. This allows you to perform logical operations on numbers while returning text or vice versa.

Example

Consider a scenario where you have a table with page URLs and their corresponding number of page views. You want to find the total number of page views only for search URLs without applying a filter to the table.

Here's the CASE function you can use:

CASE
  WHEN CONTAINS_TEXT(page_url, 'q=') THEN page_views
  ELSE 0
END

This function checks if the page URL contains the search query parameter (q=). If it does, the function returns the actual number of page views for that URL (a dynamic value). If it doesn't, the function returns a static value of 0.

By using this CASE function, you can create a separate column in your table to show the number of page views only for search URLs. The grand total of this new column will display the total number of page views for search URLs, effectively creating a segment within the table without using a filter.

In this chapter:

→ 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