cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

Accepted Solutions
Highlighted
Microsoft
Microsoft

Re: Count how many times terms appear in another column with filters

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
Highlighted
Super User IX
Super User IX

Re: Count how many times terms appear in another column with filters

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?


---------------------------------------

Putting square pegs in round holes since 1972.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Resolver I
Resolver I

Re: Count how many times terms appear in another column with filters

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

 

Highlighted
Microsoft
Microsoft

Re: Count how many times terms appear in another column with filters

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

Helpful resources

Announcements
Meet the 2020 Season 2 Power BI Super Users!

Meet the 2020 Season 2 Power BI Super Users!

Find out who's part of the program this season, and welcome the new Super Users.

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

Power BI Dev Camp - Developing with .NET Core

Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Top Solution Authors
Top Kudoed Authors