Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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 cat" appear, how many times did "fat cat" appear.
I have created a column with this formula:
WordCount = COUNTA(TableName[ColumnName])
Then I use the Matrix control putting the column contents in the Rows and the WordCount in the Values.
For reasons I don't get, any phrase that just appears once, shows up in the Matrix with a count of 7188. Can someone please help me get this right?
Thanks,
Anne
Solved! Go to Solution.
If you use the key phrase column as your row headers in a matrix and create the below measure
Proud to be a Super User!
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
Are you looking for a Power Query or DAX solution?
________________________
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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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
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:
Thank you so much for the Power Query. I will give it a shot the next time I have this assignment.
Anne
This is helpful, and it will give me more options for dealing with this next time I have to do it.
Thank you
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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
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
Hi @Anonymous
I believe the DAX measure I gave to @annetoal is working for her, it's a pretty simple solution.
Appreciate your input and I liked your Power Query suggestion.
David
Proud to be a Super User!
If you use the key phrase column as your row headers in a matrix and create the below measure
Proud to be a Super User!
That's it! Thank you so very much.
Anne
Hi Anne,
no worries, happy to help once my tired brain understood what you were needing.
Have a great day.
David
Proud to be a Super User!
Hi Anne,
will you only see a distinct phrase in each row or can you have multiple instances of different phrases in one row?
Proud to be a Super User!
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
Create a measure like below
Key Phrase Count =
CALCULATE (
COUNTROWS ( PhraseTable ),
FILTER ( PhraseTable, CONTAINSSTRING ( PhraseTable[KeyPhrase], "fat cat" ) )
)
Proud to be a Super User!
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,
my apologies, I didn't understand the nature of your request.
I'll get my thinking cap on!
David
Proud to be a Super User!
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
73 | |
67 | |
42 | |
28 | |
20 |