cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Turnipface Regular Visitor
Regular Visitor

Count keywords from text column, link them to specified categories

Hi all,

Similar to the example here (which didn't quite work for me - maybe due to the relationships) i need to do the following:
Recognize and count the number of keyword occurances from the below 3 tables:

 

Table 1

Transaction ID
1
2
3
4
5
6
7
8
9
10

 

Table 2

Transaction IDNotesCategory
1Today's purchase included apples and orangesFruit
2We had some beets and red peppersVegetable
2More text strings, and fresh orangesFruit
2lorem ipsum lorem ipsum strawberries and orangesFruit
3A treasure chest full of bananasFruit
4Turnips are not fruitsVegetable
4Colourful pretty avocadosFruit
8A table made its way downtownFurniture
9Some people hate 3-legged chairsFurniture
10A truckload of broccoli and turnipsVegetable

 

Table 3

CategoryDetails
FruitOranges, Apples, Strawberries, Avocados, Bananas
VegetableBroccoli, Red Peppers, Turnips, Beets
FurnitureTable, Chairs

 

 

This is how all 3 are related to each other

 

Relationships.png

 

More specifically, what I'm looking to do is count how many times each individual Keyword has been mentioned in the Notes section. Not only that, but keep the current link so that i can later filter by Transaction ID or any other parameter that belongs to the ID (not mentioned in Table1)

For example, when i select specific Category item like Fruit, it should count how many occurences of each of of its Keywords exist in the Notes section.

I've also got the mock .pbix file if that'll help.


What would be a good way to approach this? Any help will be highly appreciated here 🙂

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft v-ljerr-msft
Microsoft

Re: Count keywords from text column, link them to specified categories

Hi @Turnipface,

 

In this scenario, you can use the formula below to create a new measure instead, and then show the measure on the report. Smiley Happy

Measure = SUMX(Table2,COUNTROWS (
    FILTER (
        RELATEDTABLE ( 'Table3 (2)' ),
        SEARCH ( 'Table3 (2)'[Details], Table2[Notes],, 0 ) > 0
    )
))

r1.PNG

 

Regards

View solution in original post

4 REPLIES 4
Super User IV
Super User IV

Re: Count keywords from text column, link them to specified categories

You could brute force it by creating a series of columns using SEARCH function that would return a 1 if a keyword was found or a 0 if not that would also take the category into account and then another column that summed all of those columns, for example:

 

Apples = IF([Category]="Fruit",IF(SEARCH([Notes],"apple",0),1,0),0)

 


I have book! Learn Power BI from Packt


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

Proud to be a Datanaut!

Microsoft v-ljerr-msft
Microsoft

Re: Count keywords from text column, link them to specified categories

Hi @Turnipface,

 

If I understand you correctly, you should be able to follow steps below to count the keywords in your scenario.

 

1. Duplicate Table3 in Query Editor, and split the Details column by Comma to Rows 

 

s2.PNG

 

2. Trim the new Details column, and then Close&Apply.

 

trim2.PNG

 

3. Create a relationship between Table3 and the duplicated table.

 

relation1.PNG

 

4. Then you should be able to use the formula below to create a new calculate column in Table to to get the Keywords count for each Notes.

KeywordsCount = 
COUNTROWS (
    FILTER (
        RELATEDTABLE ( 'Table3 (2)' ),
        SEARCH ( 'Table3 (2)'[Details], Table2[Notes],, 0 ) > 0
    )
)

c2.PNG

r1.PNG

Here is the sample pbix file for your reference. Smiley Happy

 

Regards

Turnipface Regular Visitor
Regular Visitor

Re: Count keywords from text column, link them to specified categories

Hi v-ljerr-msft, 

Thanks for your reply! This is definitelly a move in the right direction and splitting the items in different columns is helpful!

However, the "Details" are linked to the Notes via the Category so selecting Apple, for example, will also show other results from the Notes that have the category of Fruit.

Fruits and stuf.PNG

 

What i'm looking to do is count the times when Apples, in particular, was used in the Notes. So the result of 7 up there, should in fact be 1 and linking only to the result on the bottom left.

 

What would be the thing to explore that can help me achieve that?

Microsoft v-ljerr-msft
Microsoft

Re: Count keywords from text column, link them to specified categories

Hi @Turnipface,

 

In this scenario, you can use the formula below to create a new measure instead, and then show the measure on the report. Smiley Happy

Measure = SUMX(Table2,COUNTROWS (
    FILTER (
        RELATEDTABLE ( 'Table3 (2)' ),
        SEARCH ( 'Table3 (2)'[Details], Table2[Notes],, 0 ) > 0
    )
))

r1.PNG

 

Regards

View solution in original post

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors