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
billuran2009
Helper I
Helper I

Filter Table Rows for Not top N and not bottom N

Hello, I have used the visual filter to filter top 5 Facilities based on a measure value. I have another visual for the bottom 5. How could I show the remaining Facilities? The measure is a simple count function.

1 ACCEPTED SOLUTION
v-yalanwu-msft
Community Support
Community Support

Hi, @billuran2009 ;

You could create a flag measure then apply it into filter.like belows:

flag =
VAR _top =
    TOPN ( 5, SUMMARIZE ( ALL ( 'Table' ), [Value] ), [Value], ASC )
VAR _bottom =
    TOPN ( 5, SUMMARIZE ( ALL ( 'Table' ), [Value] ), [Value], DESC )
RETURN
    IF ( MAX ( [Value] ) IN _top || MAX ( [Value] ) IN _bottom, 0, 1 )

Then apply it into filter.

vyalanwumsft_0-1632118401618.png

The final output is shown below:

vyalanwumsft_1-1632118496766.png

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
v-yalanwu-msft
Community Support
Community Support

Hi, @billuran2009 ;

You could create a flag measure then apply it into filter.like belows:

flag =
VAR _top =
    TOPN ( 5, SUMMARIZE ( ALL ( 'Table' ), [Value] ), [Value], ASC )
VAR _bottom =
    TOPN ( 5, SUMMARIZE ( ALL ( 'Table' ), [Value] ), [Value], DESC )
RETURN
    IF ( MAX ( [Value] ) IN _top || MAX ( [Value] ) IN _bottom, 0, 1 )

Then apply it into filter.

vyalanwumsft_0-1632118401618.png

The final output is shown below:

vyalanwumsft_1-1632118496766.png

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

parry2k
Super User
Super User

@Pragati11 I think the question is (if I understood it correctly), @billuran2009 already have top and bottom 5 but want to have another visual that shows the remaining facility (except top and bottom 5), correct @billuran2009 ?

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Hi @parry2k ,

 

Yes you are right. I mis-read the question. Thanks for mentioning that.

 

Thanks,

Pragati

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

Pragati11
Super User
Super User

Hi @billuran2009 ,

 

Visual level filter on a visual gives both TOP and BOTTOM capabilites.

So, you can show Bottom 5 in the same way you have applied Top 5 on your visual. 

Just try to modify your Top N  to Bottom N. There are both options available at visual level filter.

 

Thanks,

Pragati

Best Regards,

Pragati Jain


MVP logo


LinkedIn | Twitter | Blog YouTube 

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Proud to be a Super User!!

Thanks but what i would want is to sho the middle if you will. Exlude the top 5 and exlcude the bottom 5. 

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.