Topn Participants

[koorosh]

Hello all experts,

The excel data sheet (attached) shows participant names in an exam list. How I can find "topn" participants? It means, for instance, two participants who have been in more exams.

Thanks in advance.

Koorosh

Exam	Name
Microsoft	AL
Microsoft	AC
sale	AC
sale	adh
Microsoft	adu
Microsoft	ae
sale	ag
Microsoft	aj
installing	al
Microsoft	alex
Microsoft	Alexan
Microsoft	Alexander
sale	Alexander
installing	alexy
Microsoft	alexy
Microsoft	aloy
sale	aloy
installing	andre
installing2	andre
installing3	andre
market	andre
Microsoft	andre
Online	andre
sale	andre
Tech	andre
BusimessAdvanced	andre
installing3	Andy
Microsoft	Andy
sale	Andy
installing2	anna
Microsoft	anna
Online	anna
Microsoft	annie
Microsoft	antt
installing	avl
installing3	avl
Microsoft	avl
Microsoft	az
installing	Bay
installing3	Bay
Microsoft	Bay
Microsoft	ben
sale	ben
Microsoft	benh
Microsoft	beng
sale	beng
installing3	Bern
extend	Cham
Microsoft	Cham
Microsoft	cj
Microsoft	clad
installing	cob
installing3	cob
Microsoft	cob
Online	cob
Tech	cob
BusimessAdvanced	cob
Microsoft	csch
installing	dann
Microsoft	dann
Microsoft	dar
Microsoft	Deb
Microsoft	dec
installing	Den
Microsoft	Den
installing	dhi
installing3	dhi
Microsoft	dhi
Microsoft	don

[amitchandak]

@koorosh ,For Rank Refer these links

https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/3...
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

example

exam cnt =calculate(count(Table[Exam]))

Rank = RANKX(all(Table[Name]),[exam cnt])

filter rank or

Top 10 Rank = CALCULATE([exam cnt],TOPN(10,all(Table[Name]),[exam cnt],DESC),VALUES(Table[Name]))

Top 10 Rank = CALCULATE(count(Table[Name]),TOPN(10,all(Table[Name]),[exam cnt],DESC),VALUES(Table[Name]))

[koorosh]

Don't you think we should use DistinctCount instead of Count function?

[v-deddai1-msft · Accepted solution]

Hi @koorosh ,

Just create the following measure:

Rank = RANKX(ALL('Table'[Name]),CALCULATE(DISTINCTCOUNT('Table'[Exam])),,,Dense)

Then change filter of table visual:

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

Best Regards,

Dedmon Dai