TOP N in DAX

[Jaweed]

Hello friends

I would like to display the count of TOP 3 count of Staff. I cannot get it right.

My table is as below

John

John

John

John

Rob

Rob

Rob

Rob

Bill

Bill

Alex

Sam

Dough

I would like a table visual to show top 3 count of staff - Only these 3 to be displayed

Name       Count

John         4

Rob          4

Bill            2

Appreciate some help. Thanks.

[Anonymous]

Hi @Jaweed ,

HYG - 

TOP =

Var Rnk= RANKX(ALL(Sample1[Name]),CALCULATE(COUNT(Sample1[Name])),,DESC,Skip)

RETURN

IF(Rnk<=3,CALCULATE(COUNT(Sample1[Name])),BLANK())

Output:

Best Regards,
Mail2inba4

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jaweed]

@Anonymous  Thanks

[az38 · Accepted solution]

Hi @Jaweed 

add measure

r = rankx(ALL('Table'[Name]);calculate(COUNT('Table'[Name]));;;Skip)

Next, in the visual filter pane set "r is less then 4"

do not hesitate to kudo useful posts and mark solutions as solution
Linkedin

[Jaweed]

If the records are filtered on a weekno ( on each row), how can we adapt the rankr.

Ie

Name          weekno 

Bob               5

Bob               5

Bob              8

Bob              4

Tim               5

Tim               5

Tim              5

Tim              5

Bill               5

Top 2  filtered by weekno ( as per a slicer  say weekno between 4 and 5). Output is for Top 2, we should get

Name   Count of Name

Tim       4

Bob      3

Only these 2 records should appear.

Thanks to help in when filtering is added. I am figuring how to mahe first a virtual table and have the Top 2 on it, but cannot get it to work.

[az38]

@Jaweed 

my solution works good

do not hesitate to kudo useful posts and mark solutions as solution
Linkedin

[Jaweed]

Works properly. Thanks

[Jaweed]

The "ALL" in the DAX formula in filtering causes visual not to display correct records.

[Jaweed]

Thanks, but I cannot get the names.

[littlemojopuppy]

The code, not the names.  Let's see the DAX

[littlemojopuppy]

Try using the SUMMARIZE table function with Name field and COUNT(Name). 

And then use TOPN to get the top 3 from that table

[Jaweed]

Thanks @littlemojopuppy

I cannot get the dax right. Can you please assist me. Thanks

[littlemojopuppy]

This is going to be pseudocode but it should be pretty close

TOPN(3, SUMMARIZE(Table, Table[Name], "NameCount", COUNT(Table[Name])), NameCount)

[Jaweed]

Hi does not work properly. All records are populated, unless I am doing it wrong. I have the impression it gives the count , but I want the top 3 records to be populated. I cannot use filter inthe filterpane.

[az38]

@Jaweed 

one more

create measure Count:

Count = calculate(count('Table'[Name]))

next create measure r:

r = rankx(ALL('Table'[Name]);calculate(COUNT('Table'[Name]));;;Skip)

next do this

do not hesitate to kudo useful posts and mark solutions as solution
Linkedin

[littlemojopuppy]

Can you paste the code you're using?

[Jaweed]

Thanks.

Measure = TOPN(3, SUMMARIZE('attendance18-22', "Name", COUNT('attendance18-22'[Name])), [Name])

[littlemojopuppy]

Try this:

Measure = TOPN(3, SUMMARIZE('attendance18-22', 'attendance18-22'[Name], "NameCount", COUNT('attendance18-22'[Name])), NameCount)