Forum Discussion

annetoal's avatar
annetoal
Helper II
5 years ago
Solved

Counting words in a column

A table contains a column with 7188 rows. Each row contains a short phrase or a single word. I want to go through row by row and see how many times a phrase appeared. Like how many times did "house c...
Need Help
Tutorial Requests
  • Daviejoe's avatar
    Daviejoe
    5 years ago

    If you use the key phrase column as your row headers in a matrix and create the below measure

     

    Key Phrase Count =
    CALCULATE( COUNTROWS( PhraseTable))
     
    Put the measure in your matrix and that should be good.
  • Anonymous's avatar
    Anonymous
    5 years ago

    I don't know if it is possible also in DAX, but using only a function in PQ,

     

     

    starting from this data table

     

    you can get this result:

     

annetoal's avatar
annetoal
Helper II
5 years ago

Privacy rules keep me from sharing the actual file, but here is a 5-row example identical to the 7188-row file.

CompanyNameCustomerIDKeyPhrase
Madrigal Electric90120fat cat
Madrigal Electric2003754big cat
Madrigal Electric96331023mouse-killing machine
Madrigal Electric60010traffic light
Madrigal Electric9867623 fat cat

 

It should return a count of 2 for "fat cat" and every other phrase should be counted 1 time. However the way I am doing things right now, it will return a result of 10 for fat cat and every other result will be 5.

Additional information on the original data: 7188 rows, and the word "shopping" appears 5 times. The matrix report in PBI shows it as 35,940 times (7188 x 5). This is what I need help understanding. It's going back through the whole column the number of times it actually appears and multiplying it by the number of rows.

I guess I could fudge it and just divide all results by the number of rows and get the actual figure, but I want to understand the right way to do this.

 

Thanks,

Anne

Daviejoe's avatar
Daviejoe
Memorable Member
5 years ago

Create a measure like below

 

Key Phrase Count =
CALCULATE (
COUNTROWS ( PhraseTable ),
FILTER ( PhraseTable, CONTAINSSTRING ( PhraseTable[KeyPhrase], "fat cat" ) )
)

  • annetoal's avatar
    annetoal
    Helper II
    5 years ago

    Must I write a FILTER line for each phrase? Because I have thousands of different key phrases. The whole point of this is to try to identify what phrases customers use most. Is there a way to programmatically look at each row and see how many times  the phrase in it appears in the column, without my having to manually enter the phrase?

     

    Thank you for staying with me as we work through this--

    Anne

    • Daviejoe's avatar
      Daviejoe
      Memorable Member
      5 years ago

      Hi Anne,

       

      my apologies, I didn't understand the nature of your request.

       

      I'll get my thinking cap on!

       

      David