AdWords is now Google Ads. Our new name reflects the full range of advertising options we offer across Search, Display, YouTube, and more. Learn more

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

Custom Dimension

Visitor ✭ ✭ ✭
# 1
Visitor ✭ ✭ ✭



I have a table in Big Query and for simplicity lets say it has 2 columns 

id , result

The value in result could be success, fail , cancel. 


Now I want to have a table chart in Google Data Studio like following


Category        Count


Attempt          10

Success           5

Fail                   2

Cancel             3


Only issue is how to add  Attempt to the table. 


Basically what I am looking for is a condition something like 

If result =success | fail | cancel then "Attempt"

If result = success then "Success"

If result = fail then "Fail"

If result = cancel then "Cancel"


Pls. let me know if it is possible to make such Dimension.






Custom Dimension

Follower ✭ ✭ ☆
# 2
Follower ✭ ✭ ☆

Hello Naveem,

I think you cantry to create a new dimension based on a CASE WHEN + REGEXP formula to achieve your goal.


To do that, you should edit your data source and click on the "+" icon "create new field". You will call it "Category" and then the formula could look like this (Sorry I'm not 100% sure of the Regex writing).


CASE WHEN REGEXP_MATCH(result, "(success | fail | cancel)") THEN "attempt" WHEN REGEXP_MATCH(Result, "(sucess*)"  ) THEN "Success" WHEN REGEXP_MATCH(result, "(Fail*)"  ) THEN "Fail" WHEN REGEXP_MATCH(result, "(Cancel*)"  ) THEN "Cancel" ELSE "other" END


Hope it will help!

Have a nice day,


Custom Dimension

Visitor ✭ ✭ ✭
# 3
Visitor ✭ ✭ ✭

Thanks Anthony for the reply.

I tried the formula below


WHEN REGEXP_MATCH(result, "(success|failed|canceled)") THEN "Attempt"
WHEN REGEXP_MATCH(result, "(success*)" ) THEN "Success"
WHEN REGEXP_MATCH(result, "(failed*)" ) THEN "Failed"
WHEN REGEXP_MATCH(result, "(canceled*)" ) THEN "Canceled"
ELSE "other"


With this formula for all records the first When always matches the criteria and only Attempt is shown.

Success, Failed and Canceled are never shown. 


Did I miss something?


Custom Dimension

Follower ✭ ✭ ☆
# 4
Follower ✭ ✭ ☆

Hello Naveen,

Sorry for my lack of response yesterday.

I'm not 100% sure of the reason why the formula fails (probably related to the fact that the 4 conditions produce overlapping results).


I don't know if it will meet your needs, but as a workaround, you could try to build the 4 metrics individually with a unique CASE condition for each. And then, add the 4 metrics to your table.


Have a great day!