Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
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 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 🙂
@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.
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.
User | Count |
---|---|
47 | |
26 | |
21 | |
17 | |
15 |
User | Count |
---|---|
53 | |
34 | |
17 | |
17 | |
15 |