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 1Transaction ID12345678910 Table 2Transaction IDNotesCategory1Today's purchase included apples and orangesFruit2We had some beets and red peppersVegetable2More text strings, and fresh orangesFruit2lorem ipsum lorem ipsum strawberries and orangesFruit3A treasure chest full of bananasFruit4Turnips are not fruitsVegetable4Colourful pretty avocadosFruit8A table made its way downtownFurniture9Some people hate 3-legged chairsFurniture10A truckload of broccoli and turnipsVegetable Table 3CategoryDetailsFruitOranges, Apples, Strawberries, Avocados, BananasVegetableBroccoli, Red Peppers, Turnips, BeetsFurnitureTable, Chairs This is how all 3 are related to each other 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 :)

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. :smileyhappy: Measure = SUMX(Table2,COUNTROWS ( FILTER ( RELATEDTABLE ( 'Table3 (2)' ), SEARCH ( 'Table3 (2)'[Details], Table2[Notes],, 0 ) > 0 ) )) Regards

Forum Discussion

Turnipface

Advocate I

9 years ago

Solved

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 ID	Notes	Category
1	Today's purchase included apples and oranges	Fruit
2	We had some beets and red peppers	Vegetable
2	More text strings, and fresh oranges	Fruit
2	lorem ipsum lorem ipsum strawberries and oranges	Fruit
3	A treasure chest full of bananas	Fruit
4	Turnips are not fruits	Vegetable
4	Colourful pretty avocados	Fruit
8	A table made its way downtown	Furniture
9	Some people hate 3-legged chairs	Furniture
10	A truckload of broccoli and turnips	Vegetable

Table 3

Category	Details
Fruit	Oranges, Apples, Strawberries, Avocados, Bananas
Vegetable	Broccoli, Red Peppers, Turnips, Beets
Furniture	Table, Chairs

This is how all 3 are related to each other

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 :)

Need Help

v-ljerr-msft
8 years ago
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. :smileyhappy:

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

Regards

4 Replies

Greg_Deckler
Community Champion
9 years ago
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)
v-ljerr-msft
Microsoft Employee
9 years ago
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

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

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

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 ) )

Here is the sample pbix file for your reference. :smileyhappy:

Regards
- Turnipface
  Advocate I
  9 years ago
  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.
  
  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?
  - v-ljerr-msft
    Microsoft Employee
    8 years ago
    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. :smileyhappy:
    
    Measure = SUMX(Table2,COUNTROWS ( FILTER ( RELATEDTABLE ( 'Table3 (2)' ), SEARCH ( 'Table3 (2)'[Details], Table2[Notes],, 0 ) > 0 ) ))
    
    Regards