Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Scocal123
Helper I
Helper I

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

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:

WordCount
Green1
Grey1
Blue2

 

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. 

1 ACCEPTED SOLUTION
v-yiruan-msft
Community Support
Community Support

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 )

yingyinr_0-1677833608026.png

Best Regards

Community Support Team _ Rena
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

2 REPLIES 2
Scocal123
Helper I
Helper I

Thank you, amazing!

v-yiruan-msft
Community Support
Community Support

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 )

yingyinr_0-1677833608026.png

Best Regards

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.