Data Studio
1.8K members online now
1.8K 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!