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

[BenCardineau]

Hi Folks!

Considering a mock table like this one:

Option	Option_index	Sub Category	Value
Blue	1	Dark	22
Blue	1	Light	345
Red	2	Dark	456
Green	3	Medium	654

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

[BenCardineau]

Brillant, @OwenAuger , thx a lot!!

[OwenAuger · Accepted solution]

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