Count of records Based on RANKX

[Arshadjehan]

I have dynamically filtered the based based on a slicer options of:

Top 3, Top 5 and Top 10. I want to display number of records filtered , e.g when I select Top 3 , records returned after RANKX function may be 3 or more based on ties (I have used Dense option in RANKX) . So how can I show count of records on a card?

[amitchandak]

@Arshadjehan , refer if this can help

https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/3...

[Greg_Deckler]

@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. 

[Arshadjehan]

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:

Top N = RANKX(ALLSELECTED('tblResult'),[Marks],,,Dense)

I am getting the result for Top 3 in table visual as :

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

[Greg_Deckler · Accepted solution]

@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]

@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

[Greg_Deckler]

@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.