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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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)

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
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.