Data Studio
6K members online now
6K members online now
Ask questions about Data Studio
Guide Me

Problem with CASE statement

Follower ✭ ✭ ✭
# 1
Follower ✭ ✭ ✭

I am experiencing a problem with the CASE statment in the Data Studio function.


Background: I have data from a number of countries, which are reported with the dimensions "ga:country" and "ga:continent"


I need to handle Japan individually as a country, while handling all other countries as only belonging to a certain continent only. To be able to chart or tabulate that, I have created a function "

CASE WHEN ga:country  = 'Japan' THEN ga:country ELSE ga:continent END

This computed field parses fine, but when I try to use it, I get either "Japan" or "     " (empty string) as a result. it appears that ga:continent used in this context simply evaluates to "all countries except Japan" (that's what the figures show at least), but I expected the case of "not japan" to be treated exactly as if I had only used "ga:continent" in the first place.


It might be the case that this is a limitation of how the "CASE function is implemented, but from a usage point of view it is an unnatural limitation which makes it impossible to handle the data in a useful manner. Please improve.

Problem with CASE statement

[ Edited ]
Follower ✭ ✭ ✭
# 2
Follower ✭ ✭ ✭

Hi Kjell,


You're exactly right about it being a limitation of the CASE function. The root of the problem is the the CASE function can only return boolean, text, or number values -- not metric fields like we're asking it to do here. (See "Notes" section at the very bottom of the article)


I hope they fix this soon because this would be easy enough to do with even the query() function in Sheets, but in the meantime, one manual work around I think would work:


1) Manually pull a custom report in analytics like so: 

Primary dimension: Continent

Secondary dimension: Country Code

Metrics: {{metrics you need in report}}

Advanced Filter: Exclude > Country Code > {{Japan's country code}} 


2) Export to Google Sheet (All results will be on Sheet1 by default)


3) Create 2nd tab (Sheet2) and import all unique continents from Column A of Sheet1 into Column A of Sheet2 via UNIQUE('Sheet1!A:A)


4) In Column B of Sheet 2, write the formula join("|",filter('Sheet1!B2:B{eod},'Sheet1!A2:A{eod}=A2)). This will result in something like this: 

Sheet2, A2: North America / Sheet2, B2: 23423|235235|235235


5) Create your new case function with all of the data from Sheet2


CASE WHEN REGEXP_MATCH(Country ISO Code, "23423|235235|235235") THEN "North America".....[repeat for every continent].... WHEN REGEXP_MATCH(Country ISO Code, "{Japan's Code}") THEN "Japan" ELSE "Uncategorized" END. 


6) If new countries come in as 'Uncategorized' you can always find out their names / continents and add them to existing CASE function



Hope this helps!!