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
pbi_challenger
New Member

RANKX with slicer

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.

 

pbi_challenger_0-1679315951664.png

 

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

pbi_challenger_1-1679315951664.png

Very stuck at this point!. Could someone please assist?

4 REPLIES 4
amitchandak
Super User
Super User

@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: 

# Ideas = IF(ISBLANK(DISTINCTCOUNT(Ideas[Idea reference])),0,DISTINCTCOUNT(Ideas[Idea reference]))

 

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 referenceClient nameClient IndustryClient Size
Idea-1Client AIndustry AXL
Idea-1Client BIndustry BL
Idea-1Client DIndustry DS
Idea-2Client AIndustry AXL
Idea-2Client CIndustry CM
Idea-3Client AIndustry AXL
Idea-4Client AIndustry AXL
Idea-4Client BIndustry BL
Idea-4Client CIndustry CM
Idea-4Client DIndustry DS
Idea-5Client AIndustry AXL
Idea-6Client BIndustry BL
Idea-7Client AIndustry AXL
Idea-8Client BIndustry BL
Idea-9Client BIndustry BL
Idea-10Client DIndustry DS
Idea-11Client AIndustry AXL
Idea-12Client AIndustry AXL
Idea-13Client BIndustry BL
Idea-14Client DIndustry DS
Idea-15Client AIndustry AXL

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.