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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
BenCardineau
Helper I
Helper I

Why is my rankx not working when the column is sorted by another one?

Hi Folks!

Considering a mock table like this one:

OptionOption_indexSub CategoryValue
Blue1Dark22
Blue1Light345
Red2Dark456
Green3Medium654

 

I want to rank the sum of Value by 'Option' :

Sum(Blue)=367, so rank=3
Sum(Red)=456, so rank=2

Sum(Green)=654, so rank=1

 

The measure is as follow:

 

MyRank=RANKX(All(Table[Option]),CALCULATE(SUM(value)))

 

When I put in a matrix visual my 'Option' and 'MyRank', I do get the expected result. But as soon as I want to sort my column 'Option' (in column tools menu, sort column by) by 'Option_index', all MyRank values end up being 1 in my visual.

 

Do you understand that logic? I don't...

thx in advance for your inputs!

Ben

 

1 ACCEPTED SOLUTION
OwenAuger
Super User
Super User

Hi @BenCardineau 

This behaviour is described in this article:

https://www.sqlbi.com/articles/side-effects-in-dax-of-the-sort-by-column-setting/

 

In short, sort-by columns are added to a visual's DAX query along with the column being sorted, which means that modifiers such as ALL() should be applied to the sort-by column as well.

 

Try this:

MyRank=
RANKX ( 
    ALL ( Table[Option], Table[Option_index] ),
    CALCULATE( SUM ( value ) )
)

 

Regards,

Owen


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
LinkedIn

View solution in original post

2 REPLIES 2
BenCardineau
Helper I
Helper I

Brillant, @OwenAuger , thx a lot!!

OwenAuger
Super User
Super User

Hi @BenCardineau 

This behaviour is described in this article:

https://www.sqlbi.com/articles/side-effects-in-dax-of-the-sort-by-column-setting/

 

In short, sort-by columns are added to a visual's DAX query along with the column being sorted, which means that modifiers such as ALL() should be applied to the sort-by column as well.

 

Try this:

MyRank=
RANKX ( 
    ALL ( Table[Option], Table[Option_index] ),
    CALCULATE( SUM ( value ) )
)

 

Regards,

Owen


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
LinkedIn

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.