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

[jatneerjat]

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

[v-lili6-msft · Accepted solution]

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