topic Re: Why does RANKX not filter correctly? in DAX Commands and Tips

Why does RANKX not filter correctly?

Anonymous — Thu, 05 May 2022 15:25:32 GMT

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.

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

Re: Why does RANKX not filter correctly?

SpartaBI — Thu, 05 May 2022 16:22:22 GMT

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 🙂

Re: Why does RANKX not filter correctly?

Anonymous — Thu, 05 May 2022 19:46:33 GMT

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.

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

Re: Why does RANKX not filter correctly?

SpartaBI — Thu, 05 May 2022 20:11:54 GMT

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.

Re: Why does RANKX not filter correctly?

Anonymous — Tue, 10 May 2022 15:41:54 GMT

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.