cancel
Showing results for
Did you mean:
Highlighted
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

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 219 members 2,458 guests
Recent signins: