Count keywords from text column, link them to specified categories

[Turnipface]

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 🙂

[v-ljerr-msft]

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. 

Regards

[Turnipface]

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 · Accepted solution]

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. 

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

Regards

[Greg_Deckler]

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)