How to get top 5 by based on dynamic rank selection

[amikm]

Hi,

I need your quick help on below usecase
I am trying to get the top 5 countries on basis of rank (measures) and one more country irrespective of rank in my visual.

I have a bar chart which is showing all the counties name and its rank (some logic), I want to show only the top 5 countries (means starting from rank 1 to rank 5) and one country name that will be fixed always (fixed country rank can be anything)

Note: rank can be different as rank is calculated on a field that is coming from the source.

As per the above image, I want to show the top 5 countries and japan all the time irrespective its comes in top 5 or not.

To calculate rank, I am using the below measure.
Meaure = RANKX(ALL('table1'[Country]), [col1]*2 + [col2]*0.5 + [col3]*0.5, ,ASC)

[v-lionel-msft · Accepted solution]

Hi @amikm ,

Please create a measure like this and add the measure to the "Filters on this visual".

__Filter = 
IF(
    [Rank] <= 5 || MAX([Country]) = "Japan",
    1,0
)

Best regards,
Lionel Chen

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

[parry2k]

@amikm You can assign your own rank value to Japan which is part of Top 5 and once you filter on To 5, Japan will be included in it.

Meaure = 
VAR __rank = RANKX(ALL('table1'[Country]), [col1]*2 + [col2]*0.5 + [col3]*0.5, ,ASC)
RETURN
IF ( MAX ( Table[Country] ) = "Japan", 5, __rank )

Check my latest blog post Year-2020, Pandemic, Power BI and Beyond to get a summary of my favourite Power BI feature releases in 2020

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡