Counting number of times static list of text phrases appears in specific cells

[Scocal123]

Hello,

I have a dataset with a text heavy column and I also have a set list of words/phrases. I need to be able to count the amount of times they appear within cells in this column, and present it as a table.

For a column like this:

Text Column

The house is blue

It is green

I think it is Blue or Grey

I would then have a static list of words that I want to be able to put into a matrix with a count of how many times they appear within cells, like below:

Word	Count
Green	1
Grey	1
Blue	2

Is there a way to do this other than creating multiple measures for each individual text word? I was hoping to use the static list of words as a reference table but I've been struggling to figure out how to then create a measure that I can apply to them all.

Similarly, I would also like to be able to use the static list of words as a dropdown filter so that users can select it and the report will filter to show the columns with those words, but so far I've only been able to do this through using free text search filters rather than a dropdown as I'm not sure how (or if I could) join the static list of words table to the main table.

Any help would be appreciated, thank you. 

[Scocal123]

Thank you, amazing!

[v-yiruan-msft · Accepted solution]

Hi @Scocal123 ,

I created a sample pbix file(see the attachment), please check if that is what you want. 

1. Create a measure as below to get the count of text which fulfill the conditions

Count = 
VAR _selword =
    SELECTEDVALUE ( 'Words'[Word] )
RETURN
    CALCULATE (
        COUNT ( 'Table'[Text Column] ),
        FILTER (
            'Table',
            IFERROR ( SEARCH ( _selword, 'Table'[Text Column], 1, 0 ), 0 ) > 0
        )
    )

2. Create a measure as below and apply a visual-level filter on the visual with the condition(Flag is 1)

Note: It will only be suitable for the single selection, it WILL NOT working when you select multiple selections...

Flag = 
VAR _selword =
    SELECTEDVALUE ( 'Words'[Word] )
VAR _seltext =
    SELECTEDVALUE ( 'Table'[Text Column] )
RETURN
    IF ( IFERROR ( SEARCH ( _selword, _seltext, 1, 0 ), 0 ) > 0, 1, 0 )

Best Regards