Get the total number of occurences of a text from a multi-value field

[manufusana]

Hi Team,

Good day! Sorry for the question as I am new to google data studio.

We have this heatmap table with calculated fields. We are aiming to get the total number of occurences a text appeared in the 'Sub Assignee' column. Note that this column contains multiple values separated by semi colon:

However, we have no luck in finding the correct formula to get the expected results. Currently, what we have is:

CASE
WHEN REGEXP_MATCH(Sub Assignee, "Test Assignee 0") THEN 1
WHEN REGEXP_MATCH(Sub Assignee, "Test Assignee 1") THEN 1
WHEN REGEXP_MATCH(Sub Assignee, "Test Assignee 2") THEN 1
WHEN REGEXP_MATCH(Sub Assignee, "Test Assignee 3") THEN 1
WHEN REGEXP_MATCH(Sub Assignee, "Test Assignee 4") THEN 1
ELSE 0
END

It doesn't give the correct total of occurences. Assignee is the dimension we used:

We aim to tally the total number of occurrences of the Sub Assignee and place the total across the Assignee column. For example, Test Assignee 0 appeared 100 times in the Sub Assignee field, then it should put 100 in the column Total:

Is this possible? Greatly appreciate your help here.

[RebBravo]

Does anyone know the solution here? I would also like to learn how to do this for it will come in handy for our looker report! 🙂