Use Ranking to eliminate similar items get selected for TOP N using DAX

MS Power BI improves it's capabilities and evolve in every sprint. It's a very common visualization tool that comes with handy functionalities.

When using charts in Power BI, it provides the option to the developer to select top 5 records to limit the selections. This is a vary useful functionality that any developer is looking for!

When i was doing my report, i found TOP N filter gives a greater release to limit the selection. But i found another challenge. When there are different records with same value then this filter can't limit it's selection exactly to what it is set for.

As an example i need top 5 students score in a graph.

Student	Score	Top
A	70	7
B	75	4
C	72	5
D	80	3
E	72	5
F	90	2
G	72	5
H	95	1
I	70	7
J	71	6

When i choose the filter and limit the top 5 students, the graph shows me 7 records like below.

Student	Score	Top
H	95	1
F	90	2
D	80	3
B	75	4
C	72	5
E	72	5
G	72	5

But i Really needs only 5 records no matter who else have the same score like below.

Student	Score	Top
H	95	1
F	90	2
D	80	3
B	75	4
C	72	5

To resolve this issue DAX comes pretty handy. I used Ranking to rank each record so i can filter the top five records. I wrote my measures like below.

M_Scores =  DIVIDE (SUM (dimClass[Marks]) , 10 , 0)

M_Rank = 

RANKX (

ALLSELECTED ( dimStudent[StudentName]),

[M_Scores]

+ INT ( CALCULATE ( MIN (dimClass[Marks]) ) ) / 100000,

,

DESC

)

I then dragged and dropped the Ranking Measure i created into filter area and i set the item value to less than or equal to 5. 

What this Ranking measure does is it randomly assign the calculated number and make every item unique. Similarly you can also link this with date and time value to make the ranking unique.