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:
I would prefer DAX if possible. However from reading some of the things on the net I see a lot of people recommending PowerQuery. I am a complete novice at PowerQuery, and slightly less of a novice at DAX. Please, if you could provide a basic solution that a noob can handle, I would be very appreciative.
Anne
annetoal
You need the have the search words or phrases in a table.
If you have sample data, please share it.
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply š
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 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
Create a measure like below
Key Phrase Count =
CALCULATE (
COUNTROWS ( PhraseTable ),
FILTER ( PhraseTable, CONTAINSSTRING ( PhraseTable[KeyPhrase], "fat cat" ) )
)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
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