Rankx Breaking when field is being sorted?

[Roberto456]

Hello all.

I need to RankX my sales over Month/Year.

DAX is:

Year/Month Rank (Master) = IF(HASONEVALUE('(SQL) Cart'[Related_Year_Month]),RANKX(ALL('(SQL) Cart'[Related_Year_Month]),[Master],,DESC,Dense))

nothing special, but it only works when the column is not being sorted.

I need to sort it becuase when you put Month/Year on an Axis it doesnt know what month comes first, and I need to use rankX in order to colour the Data bars sales Value according to filters being selected.

Is there anyway to get Rankx to work on sorted Fields?

[Anonymous]

Hi @Roberto456 ,

Please try modifying ALL('(SQL) Cart'[Related_Year_Month]) to ALL('(SQL) Cart'). 
It's hard to find the solution only through the measure. Better to share your pbix or some sample data and expected result to us if you don't have any Confidential Information.

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Roberto456]

Before Sort by Column                                            After Sort by Column

[Roberto456]

[Anonymous]

Hi @Roberto456 ,

Based on my test, if you are using "ALL(tablename[column1])" in your formula, you can only sort by column1 and if you sort by other column, the rank result would all be 1.

If i use "ALL(table)", the result won't be affected by sort by column.

Thus, please try modifying your formula according to your table structure.

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Roberto456]

unfortunley that doesnt work because the year month field is in transaCtion table and there are multiple values.

If I rank on a calendar table the rank wont update for filters.

[Anonymous]

Hi @Roberto456 ,

Better to share your pbix to us if you don't have any Confidential Information. Otherwise it would be hard to find the problem. Please upload your files and share the link here.

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.