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
Periksson
Frequent Visitor

DYNAMIC TOP N SLICERS DISCUSSION

Hello folks,

Can someone please point me in the right direction for implementing a dynamic slicer based on a measure? I've seen many solutions on the internet, but there is always something at fault with them, some visualizations don't work with the slicer or something. Or at least if someone has a walkthrough on do's and dont's on how to implement dynamic top n slicers it would be very much appreciated.

One of the most frequent I've seen is something like this:

Top N =
VAR SelectedTop = SELECTEDVALUE('Top N'[Top N])
RETURN
SWITCH(TRUE();
SelectedTop = 0; [Measure];
RANKX( ALLSELECTED( Units[Units] );
[Measure]
)
<= SelectedTop;
[Measure])

And it's really not working for me as it should.

I've tried this one too: https://powerbi.tips/2018/02/crazy-table-gymnastics-part-2-build-support-materials/
and part 1 of this tutorial, and it doesn't work for me at all. Please if you have any info chime in.
3 REPLIES 3
amitchandak
Super User
Super User

Can you please provide more details on what you need. Not clear with your formula.

If you lloking for dynamic slicer refer:https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/50...

Hi @amitchandak, thanks for your response. I'm searching for a Top N filter slicer that works. Let me give you an example:

I want to slice products based on margin for example. From the measure I somewhat provided it would look like this:

Top N products based on margin =
VAR SelectedTop = SELECTEDVALUE('Top N'[Top N])
RETURN
SWITCH(TRUE();
SelectedTop = 0; [Margin];
RANKX( ALLSELECTED( Products[Products] );
[Margin]
)
<= SelectedTop;
[Margin])

So, when I want to expand this in my visualizations with another field from my table (for example, year), the filtering breaks and it won't filter anymore. I know that the measure depends on the ALLSELECTED() part (when I put ALL instead of ALLSELECTED it somewhat works but the filtering is very very slow then because it goes through all my records in the table).

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.