Count how many times terms appear in another column with filters

[badger123]

Hey all, hoping someone can help with this. I'm trying to count how many times each term in Table1 appears in Table2 phrase column for all the phrases selected and where the category is "A". On my page, there is a slicer with Table3 ItemID, where one ItemID can be selected at a time. Table2 and Table3 are joined together by ItemID. So if ItemID 1 is selected here, it would count the number of times each term appears in Phrase 1 and Phrase 2 and return the count against each term. 

Table1

Term

X

X

X

Table2

ItemID	Phrase	Category
1	1	A
1	2	A
1	3	B

Table3

ItemID

1

2

3

This is what I've got so far, but I can't seem to get filters to work to add the context of all selected and category A.

Measure =

VAR CurrWord =

SELECTEDVALUE ( 'Table'[term] )

RETURN

COUNTROWS (

FILTER ( Table2

, SEARCH ( CurrWord, [phrase], 1, -1 ) > 0 ) )

Any help would be greatly appreciated! 🙂 

[Greg_Deckler]

So Table 1 Term would be something like:

Red

Blue

Fish

And Table2 Phrase might be something like:

Red Fish Blue Fish

One Fish Two Fish

And you would want an output of

Red - 1

Blue - 1

Fish - 4

Is that correct?

[badger123]

@Greg_Deckler yes, exactly. But also only counting terms in the phrases in Table 2 for the selected Item ID and where Category = "A". 

[v-cherch-msft · Accepted solution]

Hi @badger123

You may drag the category column to the visual level filter like below.Then the count of your measure would be changeg by the filter.

Regards,

Cherie