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 Looker 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.
<aside> <img src="/icons/arrow-down-basic_blue.svg" alt="/icons/arrow-down-basic_blue.svg" width="40px" />
<aside> <img src="https://prod-files-secure.s3.us-west-2.amazonaws.com/f198202f-2d4c-4dd4-a450-58f6e3fb7a8b/abeb72bd-829f-44e6-b083-7e6fac3db8c7/icon-primary.svg" alt="https://prod-files-secure.s3.us-west-2.amazonaws.com/f198202f-2d4c-4dd4-a450-58f6e3fb7a8b/abeb72bd-829f-44e6-b083-7e6fac3db8c7/icon-primary.svg" width="40px" /> Enroll in Looker Studio Masterclass →
</aside>
📩 Receive my weekly Looker Studio tips
🖇 Connect with me on LinkedIn
</aside>
