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.
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.