Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi PBI community
I’ve created a RANKX measure that provides the ranking of a particular client based on the number of enhancements requests they have raised.
I’ve played around with multiple versions incl. ALLSELECTED which doesn’t seem to fit my use case because I want to only display the rank of a single client.
Current formula –
Client Global Ranking = IF(
HASONEVALUE(Ideas[Client Name]),
RANKX(ALL(Ideas[Client Name]),Ideas[# Ideas]),
BLANK())
The formula works great when I have multiple clients displayed in a table. As an example the third row below correctly displays a rank = 3.
However I want to be able to filter down to this unique client and just show that single row (or ideally in a card). But I when I apply this slicer, it just defaults to an incorrect ranking of 2 (but overall count of ideas is still correct).
Very stuck at this point!. Could someone please assist?
@pbi_challenger , remove hasonevalue and try
RANKX(ALL(Ideas[Client Name]),Ideas[# Ideas],,desc,dense)
Hi - thanks for the suggestions. Unfortuantely same issue (also if i apply skip instead of dense)
@pbi_challenger ,
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Hi - thanks for looking into this. I have a flat data structure with a +10k rows. If an idea would benefit multiple clients then that would show up across multiple rows as below.
I've created this measure to get the unique count per client:
I also have some client descriptive fields that i'm applying slicers on - eg. generate a rank based on Client size = L,XL
Table name = 'Ideas'
Idea reference | Client name | Client Industry | Client Size |
Idea-1 | Client A | Industry A | XL |
Idea-1 | Client B | Industry B | L |
Idea-1 | Client D | Industry D | S |
Idea-2 | Client A | Industry A | XL |
Idea-2 | Client C | Industry C | M |
Idea-3 | Client A | Industry A | XL |
Idea-4 | Client A | Industry A | XL |
Idea-4 | Client B | Industry B | L |
Idea-4 | Client C | Industry C | M |
Idea-4 | Client D | Industry D | S |
Idea-5 | Client A | Industry A | XL |
Idea-6 | Client B | Industry B | L |
Idea-7 | Client A | Industry A | XL |
Idea-8 | Client B | Industry B | L |
Idea-9 | Client B | Industry B | L |
Idea-10 | Client D | Industry D | S |
Idea-11 | Client A | Industry A | XL |
Idea-12 | Client A | Industry A | XL |
Idea-13 | Client B | Industry B | L |
Idea-14 | Client D | Industry D | S |
Idea-15 | Client A | Industry A | XL |
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |