Forum Discussion
Find Top N terms in a text string
I want to see how often the most common words are used in a list of movie titles.
I split, grouped, and counted the words in the titles in Power Query. I created a measure to find the TopN based on a slicer. Now, I need to look at every title and return true if it contains one of the terms in the TopN list. I don't care if this is TRUE/FALSE, or 1/0, or "Has Top Word"/BLANK - anything as long as I can filter on it for calculations.
Top N Measure calc, in case it matters (Top N Selection is a separate table for the slicer):
Top N Word =
var _TopGroup=
TOPN(
SELECTEDVALUE('Top N Selection'[Top N Selection]),
SUMMARIZE(
ALLSELECTED(TopWords[Top Words]),
TopWords[Top Words],
"Used",
SUM(TopWords[Times Used])
),
[Used]
)
var _Used = CALCULATE(
SUM(TopWords[Times Used]),
FILTER(TopWords, CONTAINS(_TopGroup,[Used],TopWords[Times Used]))
)
var _IsUsed = IF(NOT(ISBLANK(_Used)), SELECTEDVALUE(TopWords[Top Words]))
RETURN
_IsUsed
Just to follow up, someone on the EnterpriseDNA forum was able to work out how to do this. From his solution, I realized that I sometimes need to search on phrases, which made it more complicated. The back and forth with explanations is in that thread, and here's the subsequent pbix solution.
3 Replies
Hi,
I am not sure of how much i can help but i would like to try. The link you shared does not allow downloading.
Ashish_Mathur Thanks for the offer, and for flagging that you can't download it. See if this link works instead:
Just to follow up, someone on the EnterpriseDNA forum was able to work out how to do this. From his solution, I realized that I sometimes need to search on phrases, which made it more complicated. The back and forth with explanations is in that thread, and here's the subsequent pbix solution.
