Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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
@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]))
Don't you think we should use DistinctCount instead of Count function?
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |