Count text/string occurrence in a dimension

[michelle22]

I am currently working with some data which includes a list of multiple items in some rows. I would like to count the occurrence of each item in a bar chart, without having to create individual measures for each bar.

For example, my table looks like:

List

I ate an apple, I read a book

I read a book

I ran a race, I ate an apple

I ran a race

I ate an apple

I read a book, I ran a race

So "I ate an apple" = 3 ; "I read a book" = 3 ...

I know that I can create individual bars using a measure like Count({<List={"*I ate an apple*"}>} List).

However, what I would like to do is just have to use one dimension and measure to accomplish this. Is this possible?

I almost had it with the following:

Dimension = Valuelist('*I ate an apple*','*I ran a race*','*I read a book*') and 

Measure = Count(If(WildMatch(List, Valuelist('*I ate an apple*','*I ran a race*','*I read a book*')), List))

But the asterisks then displayed in the chart. Any ideas?

[SerhanKaraer · Accepted solution]

Hello Michelle,

My advice is to create a second table by splitting the multiple records of list into rows.

Yet, you still can achieve what you want in the chart expressions as:

dimension: Valuelist('I ate an apple','I ran a race','I read a book')

measure: pick(match(Valuelist('I ate an apple','I ran a race','I read a book'),'I ate an apple','I ran a race','I read a book'),

Count(If(WildMatch(List, '*I ate an apple*'), List)),
Count(If(WildMatch(List, '*I ran a race*'), List)),
Count(If(WildMatch(List, '*I read a book*'), List))
)

Regards,