Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more

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?

5 REPLIES 5
baltemus
Frequent Visitor

Hi, was this ever resolved?  I am running into a similar issue where when a slicer for the same dimension I am ranking over is applied, the rank value for some of the dimensions when you filter down to one dimension value at a time changes/produces an incorrect result for their ranking.

amitchandak
Super User
Super User

@pbi_challenger , remove hasonevalue and try

 

RANKX(ALL(Ideas[Client Name]),Ideas[# Ideas],,desc,dense)

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

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.

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

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
PBIApril_Carousel

Power BI Monthly Update - April 2025

Check out the April 2025 Power BI update to learn about new features.

Notebook Gallery Carousel1

NEW! Community Notebooks Gallery

Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.

April2025 Carousel

Fabric Community Update - April 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors