show top 5 and bottom 5 data on the map chart

[lilySixteen]

Hello,

I have a map chart that shows the year-over-year changes in % for cities from the 8th week of 2020 to the 23th week of 2020. Now, I want to filter out the top 5 cities and bottom 5 cities as I change the selected week.

For example, below is the map chart for cities. The green bubble indicates the city has positive change and red means negative change. The bubble size varies depending on the value (YoY change %). So, let's say, if Seatle/DC/Chicago/Dallas/NYC are the top 5 cities in terms of the value in week 8, I only want them to show on the map. 

Here is my City table. Btw, the data in the Value column is actually in %. Direction column only contains data 1 and 2:  1 means the value in the Value column is negative and will be a red bubble on the map, and 2 means positive and will be a green bubble on the map. The size column decides the bubble size. Week column calculates the week# . 

Can anyone please help me with this? Thanks so much!

Lili

[amitchandak]

@lilySixteen , you can use rank and filter for top and bottom 5

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/3...

[lilySixteen]

@amitchandak thank you for your answer! I have tried RANKX but don't know how to apply it to the map. Can you please provide me details? Thasnks!

[amitchandak · Accepted solution]

@lilySixteen , Try something like this. All measures

rankd = RANKX ( ALLSELECTED( 'City'[City] ), [YOY%], , asc,dense )
ranka = RANKX ( ALLSELECTED( 'City'[City] ), [YOY%], , asc,dense )

final Rank =CALCUALTE([YOY %], filter('City',rankd<=5 || ranka >= 5))

Also refer if you can use visual level filter

https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...

[lilySixteen]

@amitchandak Thank you so much for your help. It answered my question. Very helpful!