cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
danextian New Contributor
New Contributor

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

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.

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

I would greatly appreciate your help.

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa
1 ACCEPTED SOLUTION

Accepted Solutions
danextian New Contributor
New Contributor

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

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

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa
1 REPLY 1
danextian New Contributor
New Contributor

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

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

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
www.linkedin.com/in/danebelarminocpa

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 272 members 2,820 guests
Please welcome our newest community members: