cancel
Showing results for
Did you mean:
Member

## How to show top 50 and bottom 50 records in a table

Hi

How can I apply a visual level filter on a table so that the table would show top 50 and bottom 50 application based on number of users.

currently once i applied bottom 50 filter then it wont allow me to add another visual level filter. so how can i get top 50 and bottom 50 app

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: How to show top 50 and bottom 50 records in a table

hi, @jatneerjat

You could try this simple way as below:

Step1:

Use RANKX function to add two measure, one is based on ascending order and another is based on descending order.

`top = RANKX(ALLSELECTED(Table1),CALCULATE(SUM(Table1[Qty])),,DESC)`
`bottom = RANKX(ALLSELECTED(Table1),CALCULATE(SUM(Table1[Qty])),,ASC)`

Step2:

Create this filtering measure

`Measure = IF([bottom]<=50||[top]<=50,1,2)`

Step3:

Drag it into visual level filter and set filter is 1.

Result:

In my simple sample, I want to show top 2 and bottom 2 records in a table

here is sample pbix file, please try it.

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support Team

## Re: How to show top 50 and bottom 50 records in a table

hi, @jatneerjat

You could try this simple way as below:

Step1:

Use RANKX function to add two measure, one is based on ascending order and another is based on descending order.

`top = RANKX(ALLSELECTED(Table1),CALCULATE(SUM(Table1[Qty])),,DESC)`
`bottom = RANKX(ALLSELECTED(Table1),CALCULATE(SUM(Table1[Qty])),,ASC)`

Step2:

Create this filtering measure

`Measure = IF([bottom]<=50||[top]<=50,1,2)`

Step3:

Drag it into visual level filter and set filter is 1.

Result:

In my simple sample, I want to show top 2 and bottom 2 records in a table

here is sample pbix file, please try it.

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Community News & Announcements

Get your latest community news and announcements.

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 418 members 4,475 guests
Recent signins: