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:
Hi annetoal
"
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."
I don't know DAX and I don't know the formulas you use, but it seems to me, from the examples given, that if you divide the count you do by 5 or, in general, by the number of rows in the table(7188?), you get the result you are looking for.
Isn't that what it is?
Seems to be! This was what I was referring to as a "fudge."
Thank you,
Anne