To display preceding and succeeding values from the slicer selection

[jayasurya_prud]

I am working on a problem statement. Where I am creating a map and tables and slicers for filters. 

Let's say that my fields are

city

city id

customers count

sales

profit

category

in power bi, I am creating a map visual, where the city will be pointed and customers count, sales, profit will be mentioned as tooltip.

What I am trying to do is, lets say there are 10 categories and each category has 20 records/cities. 

What i want is, if I select a particular record/city, only the preceding 5 records/cities in the data and succeeding 5 records/cities in the data should be appeared in the map and the table,  along with the city that I selected in the slicer.  (like preceding and unbounded following in sql). 

Please help me with the solution. For more clarity, reply your questions. 

[v-yadongf-msft · Accepted solution]

Hi @jayasurya_prud ,

This is my test table:

Create a new column:

Rank = RANKX(FILTER('Table','Table'[City] = EARLIER('Table'[City])),'Table'[Profit],,ASC,Dense)

Add [Rank] column to filters and filter data like below shown:

Then you can show the preceding 5 records/cities in the data and succeeding 5 records/cities in the data.

Best regards,

Yadong Fang

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