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

Why does RANKX not filter correctly?

I have a table the compares current counts to last month's counts and shows the percentage of change between them.  Using code from Marco Russo, I created the ranking formula below, based on the Diff %, which seems to be correct.

 

Ruthie09_0-1651763834731.png

 

_Inc Count Diff % PMs to SP Rank =

IF (

    ISINSCOPE( 'INCIDENTS'[INC_CI_SERVICE] ),

    RANKX (

        CALCULATETABLE (

            VALUES ( 'INCIDENTS'[INC_CI_SERVICE] ),

            ALLSELECTED ( 'INCIDENTS'[INC_CI_SERVICE] )

        ),

        'Incident Measures'[_Inc Diff % PMs to SP]

    )

)

 

So far, so good....until I then filter on Rank less than 6 (I need the top 5).  When I do that, it shows only the first row vs showing the 5 rows through and including PeopleSearch.

 

Is there some aspect of RANKX that disallows ranking and filtering on percentage values?  Why won't this filter correctly?

 

Any help would be appreciated.

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

The problem turned out to be ther other filter (_Inc Count is greater than 10).  PBI is applying filters in the "wrong" order.  It is ranking the items so if you filter on Rank less than 6, it returns that.  Then it applies the _Inc Count is greater than 10.  If none of the items in the top 5 have more than 10 tickets, it returns nothing.

 

What I need it to do is reverse that order of application.  So I'm going to create a new thread for that.

 

So this isn't so much a solution as it is an explanation.

View solution in original post

4 REPLIES 4
SpartaBI
Community Champion
Community Champion

@Anonymous where do you apply this filter? On the visual level filters? You add code to the formula?

Also, please try first writing this version of the formula (not sure it will work, but it's much better to write it this way. It should be the same, but maybe it's the reason for the issue because of Shadow Filters when you use the allslected in the way you used it):

_Inc Count Diff % PMs to SP Rank =

IF (

    ISINSCOPE( 'INCIDENTS'[INC_CI_SERVICE] ),

    RANKX (

         ALLSELECTED ( 'INCIDENTS'[INC_CI_SERVICE] ),

        'Incident Measures'[_Inc Diff % PMs to SP]

    )

)

 

In case it answered your question please mark this as a solution for community visibility. Appreciate Your Kudos 🙂

Anonymous
Not applicable

@SpartaBI  Yes, I have 2 visual level filters.  One weeds out CI Services with less than 10 tickets and the other is to filter for ranks.

 

Ruthie09_0-1651779943115.png

 

I tried your formula but it didn't make any difference.

 

 

 

Anonymous
Not applicable

The problem turned out to be ther other filter (_Inc Count is greater than 10).  PBI is applying filters in the "wrong" order.  It is ranking the items so if you filter on Rank less than 6, it returns that.  Then it applies the _Inc Count is greater than 10.  If none of the items in the top 5 have more than 10 tickets, it returns nothing.

 

What I need it to do is reverse that order of application.  So I'm going to create a new thread for that.

 

So this isn't so much a solution as it is an explanation.

@Anonymous ok, need to go deeper. It's already late night here. I'm from mobile now. Will take a look at it again tomorrow.

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.

Top Solution Authors