Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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.
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.
@Ashish_Mathur Thanks for the offer, and for flagging that you can't download it. See if this link works instead:
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.
User | Count |
---|---|
124 | |
106 | |
99 | |
63 | |
59 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |