Forum Discussion
Counting words in a column
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.
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:
Privacy rules keep me from sharing the actual file, but here is a 5-row example identical to the 7188-row file.
| CompanyName | CustomerID | KeyPhrase |
| Madrigal Electric | 90120 | fat cat |
| Madrigal Electric | 2003754 | big cat |
| Madrigal Electric | 96331023 | mouse-killing machine |
| Madrigal Electric | 60010 | traffic light |
| Madrigal Electric | 9867623 | 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
Hi Anne,
will you only see a distinct phrase in each row or can you have multiple instances of different phrases in one row?
In this case, each row will only have one phrase. It might be a few words or sometimes just one word: "shipping." 7,188 rows, some with commonly-used phrases, some with one-off phrases, some with one single word. I need to identify the phrases customers mention most.
Thank you
Anne