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
pathomson
Regular Visitor

RANKX Formula is missing a ranking number

I have several Rankings based off a single table of categories that is linked to other data tables. Some of my results show the ranking from 1 to 46, but others show 1 to 47 with a ranking number skipped.

I am using a measure for the value and using RANKX(All...for the category table to rank against.

Any ideas why this is happening when the formual for each ranking is the same and all tables are linked in the same manner?

1 ACCEPTED SOLUTION

Thanks,

 

I have found the issue and the resolution throught trial and error.

I had 5 different tables that I am ranking, and when they are joined, there are stations that are not in the master list of stations I want to rank by. To solve this issue, instead of the ALL function, I have changed it to ALLNONBLANKROW function.

Now I am receiving the correct value of 46 as the highest instead of 47.

View solution in original post

9 REPLIES 9
v-shex-msft
Community Support
Community Support

HI @afzalphatan,

 

If your table contains same values, rankx function will return same ranking value.

 

For Skip option, it will mark same value as same ranking, then marking next ranking as current ranking count.(e.g. 1,1,1,4,5,5,7,8...)

For Dense option, it will ranking values without skip same ranking number.(e.g. 1,1,1,2,3,3,4,5...)

 

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

HI @v-shex-msft

 

I am having the issue that you have describe using the Skip option I wish for my data to be displayed in the Dense option solution.

 

Can you provide more context around how to use the Dense option please as I am usure as to what you mean but it is what I need.

 

Thanks,

Ricky

At the end of your formula (the last option) you have the option to use skip or dense. the skip will pass all ties and show the next number in the ranking. the dense will use the next number.

So if you have a ranking of 5 values and number 2 and 3 are tied scores, it will go 1,2,2,4,5. If you use dense it will go 1,2,2,3,4.

There is some help on the microsoft page below whjich may explain it better than I can:

 

https://msdn.microsoft.com/en-us/query-bi/dax/rankx-function-dax

Anonymous
Not applicable

That worked perfectly. Thanks!!

Thanks,

 

I have found the issue and the resolution throught trial and error.

I had 5 different tables that I am ranking, and when they are joined, there are stations that are not in the master list of stations I want to rank by. To solve this issue, instead of the ALL function, I have changed it to ALLNONBLANKROW function.

Now I am receiving the correct value of 46 as the highest instead of 47.

afzalphatan
Resolver I
Resolver I

Just show the expected result for SMALL data set (pic in excel would be great) .... the logic will be same for any file size.....

Capture.PNG

Above is the current results. However, it shoud read 46, 45, 44.

Beow is the data table. However, the report page is filtered for the last 12 calendar months using a calendar table which is linked to this data table.

This data table is linked to the Ranking Station Table.

 

So there should be 3 stations that match for the last 12 calendar months, and the Ranking station table has 46 Stations.

Thus the Ranking should be 46, 45, 44 then all the remaining stations should be 1.

 

Capture.PNG

v-shex-msft
Community Support
Community Support

HI @pathomson,

 

It will be help if you provide some sample data with measure formula and expected result.

In addition, I'd like to suggest you use Dense as rankx option parameter, it does not skip same value ranking.

 

Regards,

Xiaoxin Sheng

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

The file is too large to share. One of the sinple rankings is based on a table of stations with delays and the following measure:

 

Total Delay Minutes = sum('Delays Outstations'[Delay Minutes])

 

The Ranking measure is as follows:

 

Rank Delays by Station = if(hasonevalue('Catering Stations for Ranking'[Stations for Ranking]),
rankx(all('Catering Stations for Ranking'[Stations for Ranking]),[Total Delay Minutes],,ASC,skip))

 

The table: Stations for Ranking has 46 Stations, and ther are 3 stations with delay minutes for the date range (last 12 calendar months).

 

The results have the value 47, 46, 45, then the remaining stations with zero delay minutes all as 1.

Other rankings have a number for each station, but skip a number.

 

The tables have a relationship with the station in the data table and the table with Stations for Ranking (one to many)

I only want to rank the stations that match with the Stations for Ranking.

 

If you need more informationm, please let me know.

 

Thanks.

 

 

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.