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"))
Arshadjehan - I would think you should just be able to use:
Measure = COUNTROWS('Table')Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
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.