RANKX not working when a text categor is being sorted by another category

[danextian]

Hi All,

It seems to be a weird behaviour of Rankx. I'm trying to rank Day Name based on a certain measure.

My formula is 

DayNameRank = 
rankx(ALL('Case'[DayName]),[Measure])

Since the column being ranked is a text, Power BI sorts its items alphabetically. So I use day of week to custom sort the day names.  The above formula works fine if the Day Name column is sorted by itself but returns 1 if sorted by day of week.

Image 1 - Day Name is sorted by Day of Week Number, Image 2 - Sorted by itself

I would greatly appreciate your help.

[danextian · Accepted solution]

I searched the web and found out that this is indeed a behavior of RANKX. This can be fixed by including the sort column to the formula. So it should have been:

DayNameRank =
RANKX ( ALL ( 'Case'[DayName], 'Case'[Day of Week] ), [Measure] )

The explanation is on sqlbi