Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
GSmithDesign
Frequent Visitor

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.

Sample PBIX

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.

Desired outcome.png


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

 

1 ACCEPTED SOLUTION
GSmithDesign
Frequent Visitor

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.

View solution in original post

3 REPLIES 3
GSmithDesign
Frequent Visitor

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.

GSmithDesign
Frequent Visitor

@Ashish_Mathur Thanks for the offer, and for flagging that you can't download it. See if this link works instead:

 

https://1drv.ms/u/s!AvVZRhaqM6oUoWbvMpr8GFNt6UWA?e=G7d4cZ

Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.