cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
badger123
Resolver I
Resolver I

Count how many times terms appear in another column with filters

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

ItemIDPhraseCategory
11A
12A
13B

 

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! 🙂 
1 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.

1.png

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
Greg_Deckler
Super User
Super User

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?


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

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

 

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.

1.png

Regards,

Cherie

Community Support Team _ Cherie Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors