Count records that contain text from each row

[morgtd30]

I'd like to get a count of the amount of times different strings show up in another table.

Table of search values:

ID    Text
1     Apple

2     Banana

3     Grapes

4     Orange

Table of data:

John	Apple, Banana
Henry	Apple, Banana
Jake	Orange
Jennifer	Apple
Jackie	Grapes, Orange
Mike	Apple, Grapes

How do I have the first table count this as the end result?:

ID    Text        Count
1     Apple         4

2     Banana       2

3     Grapes        2

4     Orange       2

[HotChilli]

For the full solution (involving filtering) you could split the column by delimiter, -> To Rows

then create a relationship using the first table as a dimension table

[morgtd30 · Accepted solution]

I achieved it with this calculated column:

Countif = COUNTX(

FILTER(Customers,CONTAINSSTRING(Customers[purchases],'Produce'[text)),1)

Now I'd love to be able to filter by the produce types but that's a different problem.

[AbbasG]

@morgtd30 there is no primary key between the two tables, how do you connect them?