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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
neeraj068
New Member

Issue with RANKX Function

Hi Team,

 

We are facing an issue when using the RANKXX function to calculate the Rank. We have a dataset that contains the Hotel Sales at each hotel that falls in a State. When all the Hotels or all the States are selected through the slicer, RankXX works fine but when only one state is selected through the slicer, even though Rank should be 1 for one particular state but it displays it as 2.

Measures Definition area as follow 

 

SRS_New = IF( HASONEVALUE(ParkNames[Park Codes-Park Name]),BLANK(),Sales_aggregateData[Sales Rank By State_new])

 

Sales Rank By State_new = IF (
ISBLANK(Sales_aggregateData[Booking_Total_SUM]),
BLANK(),CALCULATE(RANKX (ALL(States[States]),Sales_aggregateData[Booking_Total_SUM],,DESC),ALL(ParkNames[Park Codes-Park Name])))

 

Below is the sample data

 

State  ParkName Booking_Total_SUM

ABC   ABC_123    100

ABC   ABC_456    150

DEF   DEF_123    50

ABC   DEF_456    70

 

 

Please let us know if any more information is needed.

 

Thanks,

Neeraj 

 

1 ACCEPTED SOLUTION
v-kelly-msft
Community Support
Community Support

Hi  @neeraj068 ,

 

Modify your measure as below:

Measure = 
RANKX(ALLSELECTED('ParkNames'),CALCULATE(SUM('ParkNames'[Booking_Total_SUM])),,ASC,Dense)

And you will see:

v-kelly-msft_0-1616747396196.pngv-kelly-msft_1-1616747614128.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

View solution in original post

3 REPLIES 3
v-kelly-msft
Community Support
Community Support

Hi  @neeraj068 ,

 

Modify your measure as below:

Measure = 
RANKX(ALLSELECTED('ParkNames'),CALCULATE(SUM('ParkNames'[Booking_Total_SUM])),,ASC,Dense)

And you will see:

v-kelly-msft_0-1616747396196.pngv-kelly-msft_1-1616747614128.png

For the related .pbix file,pls see attached.

 

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

Jihwan_Kim
Super User
Super User

Hi, @neeraj068 

I am not sure if I correctly understand your DAX measure, but please try to use ALLSELECT , instead of ALL, in front of (States[States]).

When you want to filter the ranking by the states-slicer, then I believe ALLSELECT is the correct option.

I hope this helped.

Or, please kindly share your sample pbix file then I can try to have a look and come up with a desirable solution.

 

Thank you very much.

 

 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

Angith_Nair
Continued Contributor
Continued Contributor

Hi @neeraj068 ,

Could you please provide your pbix file so that I can look into it...?

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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