Forum Discussion
Post PartisanTopn Participants
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 |
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
3 Replies
- amitchandak
Super User
koorosh ,For Rank Refer these links
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415
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-finaleexample
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-msftCommunity Support
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
