Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

How to fix ties when Top 5 is used in Visual Filters ?

Hi Team,

 

I have gone several posts before posting this Question in Community and didnt find a proper solution related to Top 5 and breaking ties . 

 

So I wanted to resinstate discussion on this and check if there is solution for the issue. 

 

I have date like below : 

 

Product    IncCount 

ABC          590

BCD          45

BEF           10

JSH            2

JKS             1

SJS             1 

 

When I apply Top 5 in visual filters ,I still see 6 rows as an output because of ties between JKS and SJS products. 

 

Could some one please help me to fix this issue ?  


I also tried creating a DAX code using Rank ,but failed . 

 

Index = RANKX(ALL('Closed & Open INC'[Product]),COUNT('Closed & Open INC'[Incident_Number]) )
top 5 = CALCULATE(TOPN(5,VALUES('Closed & Open INC'[Product]),[Measure] +RAND()))
1 ACCEPTED SOLUTION
Icey
Community Support
Community Support

Hi @Anonymous ,

You can refer to this post: How to Rank a list based on 2 values? double rankX?

And you can create your measures like so:

Primary Measure = MAX('Closed & Open INC'[IncCount])
Second Measure = MAX('Closed & Open INC'[Product])
Measure = 
RANKX (
    ALL ( 'Closed & Open INC' ),
    RANKX ( ALL ( 'Closed & Open INC' ), [Primary Measure],, ASC )
        + DIVIDE (
            RANKX ( ALL ( 'Closed & Open INC' ), [Second Measure],, DESC ),
            ( COUNTROWS ( ALL ( 'Closed & Open INC' ) ) + 1 )
        )
)

rank.PNG

Best Regards,

Icey

 

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

View solution in original post

2 REPLIES 2
nancmadi
Advocate II
Advocate II

I am trying to use this but I am not understanding where your column name 'column' is coming from??
Nothing is stated here about 'column'

Icey
Community Support
Community Support

Hi @Anonymous ,

You can refer to this post: How to Rank a list based on 2 values? double rankX?

And you can create your measures like so:

Primary Measure = MAX('Closed & Open INC'[IncCount])
Second Measure = MAX('Closed & Open INC'[Product])
Measure = 
RANKX (
    ALL ( 'Closed & Open INC' ),
    RANKX ( ALL ( 'Closed & Open INC' ), [Primary Measure],, ASC )
        + DIVIDE (
            RANKX ( ALL ( 'Closed & Open INC' ), [Second Measure],, DESC ),
            ( COUNTROWS ( ALL ( 'Closed & Open INC' ) ) + 1 )
        )
)

rank.PNG

Best Regards,

Icey

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.