topic Re: RankX ordered by Text in Category in DAX Commands and Tips

RankX ordered by Text in Category

Anonymous — Sun, 17 Jan 2021 16:44:48 GMT

Hi,
Is it possible to make RankX column ordered by Name in Category
like in the picture

My try is not working...

Name_Rank =
RANKX(
FILTER('Table','Table'[name]=EARLIER('Table'[name])),
CALCULATE(
max(
'Table'[name]
),
ALLEXCEPT(Table,Table[category],Table[name])
),,DESC,Dense
)

What I need to insert in Calculate ??

Re: RankX ordered by Text in Category

themistoklis — Sun, 17 Jan 2021 18:24:03 GMT

Hello Anonymous ,

Please try the following formula:

Rank = RANKX ( FILTER ('Table', 'Table'[Category] = EARLIER('Table'[Category]) ), -- Grouping Column 'Table'[Name], -- Sorting Column , ASC )

Re: RankX ordered by Text in Category

Anonymous — Sun, 17 Jan 2021 19:23:56 GMT

Hi,
This good solution, but I have other columns in the table (this table only an example)
So I think needs to be any "all" function in the "calculate" function, but in calculate is possible using only aggregation...

Re: RankX ordered by Text in Category

themistoklis — Sun, 17 Jan 2021 19:51:19 GMT

Anonymous

You can try the following measure:

If you want to add more filters make sure you declare the variable on top and then insert it in the ALLSELECTED area.

The following returns the same results

Sorting Rank = VAR _category = MAX ('Table'[Category] ) --VAR _secondvariable = MAX ( Sheet1[p_type] ) Return RANKX ( FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Category] = _category -- && Sheet1[p_type] = current_type ), CALCULATE ( MAX ( 'Table'[name] ) ), , ASC, DENSE )