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
Anonymous
Not applicable

RankX to only a certain level

Hello! I am using RankX to determine my top customers. That formula is working really well for some applications, but poorly for others.

 

For instance, I have a matrix with some information about the customers and then these figures split out beneath by brand. I then want to set the table to only show the top 15 customers, by using a Top N filter on the ranks.

 

This method works well whent the brands aren't shown beneath. However, with the brands in the table, it is showing more items than planned because it's also giving rankings at brand level. How can I get RankX to only give me one value - a ranking at the customer level?

 

Right now my code is as follows, where a "Chain Parent" is a customer. adding filters on all brands don't seem to fix it.

 

Customer Rank = RANKX(ALLSELECTED(f_RAD[Chain Parent]),[LTM 9L Cases],,DESC)

 

I am Live Connected to Analysis Services, so I am running into the issue that I can't add calculated columns, unfortunately.

 

Thank you in advance!

 

Hannah

1 ACCEPTED SOLUTION

HI @Anonymous ,

You can take a look at following link to know how to use if statement to check current hierarchy level:

Clever Hierarchy Handling in DAX

Sample of ranking based on hierarchy drill down:

DAX – Ranking with Hierarchy Drill-Down

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

4 REPLIES 4
v-shex-msft
Community Support
Community Support

HI @Anonymous ,

It is impossible to use one formula to handle multiple level ranking, you need to add if statement to check current row contents level and write correspond ranking formula based on current category group.

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Anonymous
Not applicable

Thank you very much! Can you give me an example of what that might look like? I have tried adding an IF statement and couldn't get it to produce a different result.

 

Thank you,

Hannah

Anonymous
Not applicable

Just following up to see if you can still help here! Thanks.

HI @Anonymous ,

You can take a look at following link to know how to use if statement to check current hierarchy level:

Clever Hierarchy Handling in DAX

Sample of ranking based on hierarchy drill down:

DAX – Ranking with Hierarchy Drill-Down

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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.