Forum Discussion
Count of records Based on RANKX
Arshadjehan - Given the form of that RANKX calculation it must be a calculated column. Therefore, this should be something like the following:
Male = COUNTROWS(FILTER('Table',[Top N] <= 3 && [Gender]="M")) Female = COUNTROWS(FILTER('Table',[Top N] <= 3 && [Gender]="F"))
Here is the sample data: tblResults
| Auto ID | Roll No | Name | Marks | Gender |
| 1 | 12324 | ABC | 515 | M |
| 2 | 2323 | LMN | 525 | M |
| 3 | 23234 | XYN | 535 | F |
| 4 | 65655 | DEF | 525 | M |
| 5 | 345345 | PRS | 510 | F |
| ............. |
Table has more than a million records.
As a first step I have to list only Top 3, Top 5 or Top 10 records
I am doing that by applying RANKX function as below:
| Rank | Roll No | Name | Marks | Gender |
| 3 | 12324 | ABC | 515 | M |
| 2 | 2323 | LMN | 525 | M |
| 1 | 23234 | XYN | 535 | F |
| 2 | 65655 | DEF | 525 | M |
Next I want to display number of student from Top N being Male or female on card visual as:
Male: 3
Female:1
Hope I have elobarated well now
Arshadjehan - Given the form of that RANKX calculation it must be a calculated column. Therefore, this should be something like the following:
Male = COUNTROWS(FILTER('Table',[Top N] <= 3 && [Gender]="M"))
Female = COUNTROWS(FILTER('Table',[Top N] <= 3 && [Gender]="F"))
Greg_Deckler That worked like a charm! Thanks man.
Just one thing needed: Since I am using Dense parameter in RANKX function , so i am having ties in the result. How can I add sequential serial number in the table vaisual as below:
Serial No Position Name Marks 1 1 ABC 545 2 2 DEF 535 3 2 GEF 535 4 3 LMO 525 5 3 XYZ 525 Arshadjehan - So, generally adding an index in DAX is considered impossible. However, there is a method for doing it.
https://community.powerbi.com/t5/Quick-Measures-Gallery/The-Mythical-DAX-Index/m-p/1093214#M528
The other way would be to add a tiny random number to your rank/topn calculation
Top N = RANKX(ALLSELECTED('tblResult'),[Marks],,,Dense) + RANDBETWEEN(.001,.009)and then you could have a column:
Index = COUNTROWS(FILTER('Table',[Top N]<=EARLIER([Top N])))This will, in effect break your ties.