Counting the number of occurrences of a string in different columns

[KCfromDC]

Hi all-

I've gone around in circles trying to get this to work and am at my wit's end. I'm sure it's an easy solution, but I'm still learning PoweBI and none of my searches have come up with an answer.

I have a table coming from a system where the user can add up to three tags to a record. Unfortunately, all of the tags get combined into one column, and they're not in alphabetical order. Fortunately, they are delimited by a comma, so I was able to split them across three columns:

A.1	A.2	A.3
Apple	Banana	Carrot
Apple	Carrot
Banana	Apple	Carrot
Carrot
Banana	Carrot	Apple
Carrot	Banana

I've been trying to count the total number of occurences of each tag in the table as a measure. (E.g., total # of "Apple" = 4, Bananas = 4, Carrot = 5).

Unfortunately, using a filter causes the other two columns to not be counted - for example, column A.1 filtered to "Apple" will not count the occurrences in rows #3 and #5 (columns A.2 and A.3, respectively) as it can't "see" them.

I've tried using the following to clear the filter, but it still only counts what's in the first column.

AppleCount = CALCULATE(
COUNROWS(FILTER(Table,[A.1]="Apples"))) +
CALCULATE(
COUNTROWS(FILTER(ALL(Table),Table[A.2]="Apples")))

Any help is much appreciated. Thank you!

[v-shex-msft]

Hi @KCfromDC,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[smpa01 · Accepted solution]

@KCfromDC  I answered a similar question Here 

[KCfromDC]

Hi all-

Thank you - not sure if this will work, though, as it's a 3,000+ row dataset with 10 columns. I wanted to use the above as a simpler example, so unpivoting them may not work... 😕

[NidhiBhusari]

Hi @KCfromDC ,

Go to power Query and unpivot the columns then add conditional column to assign ID to the tags. AFter following these steps you will get below table.

Then drag ID's column(Take count of it) in Table visual.

[amitchandak]

@KCfromDC , When you split them , you can split rows, in this case that would be a better option

Split Column Power Query: https://youtu.be/FyO9Vmhcfag