topic Avoid Context When Ranking in DAX Commands and Tips

Avoid Context When Ranking

Anonymous — Tue, 20 Aug 2024 13:17:41 GMT

Hi ,
I want to rank my table based only on the Name column, even if I bring in any number of columns into the context (like Column2, Column3). The measure I use now removes the context from Column1, Column2 and im getting wat i want, but the ranking is not continuous; it is giving the row number from the temporary table. How can I make it continuous?

The measure I used:

RANK - TEST =
RANKX(
ALL(D_TEST),
CALCULATE(MAX(D_TEST[NAME])),,
ASC,
Dense
)

I also tried using ALLSELECTED instead of ALL. The ranking is wrong, when I have more than one column in context.

Note: The goal is to achieve ranking based only on the first column (based on NAME column in ASC Order) in a matrix table. ignoring other columns in context.

output of the above dax:

NAME	COLUMN2	COLUMN3	RANK - TEST
assistant1	A	Jul-24	20
assistant1	B	Jul-24	20
assistant1	B	Aug-24	20
assistant2	A	Jul-24	62
assistant2	B	Jun-24	62
assistant2	B	Jul-24	62
assistant2	B	Aug-24	62
assistant2	C	Jul-24	62
assistant3	A	Jul-24	302
assistant3	B	Jul-24	302
assistant3	B	Aug-24	302

Re: Avoid Context When Ranking

OwenAuger — Tue, 20 Aug 2024 13:55:15 GMT

Hi Anonymous

Since you want to rank just by the NAME column, try either of these, assuming you want the rank to be in ascending order of D_TEST[NAME]:

RANK = RANK ( DENSE, ALL ( D_TEST[NAME] ) )

RANK = RANK ( DENSE, ALLSELECTED ( D_TEST[NAME] ) )

For descending order, add an ORDERBY argument, e.g.

RANK = RANK ( DENSE, ALL ( D_TEST[NAME] ), ORDERBY ( D_TEST[NAME], DESC) )

Do the above work for you?

Re: Avoid Context When Ranking

Anonymous — Tue, 20 Aug 2024 15:25:56 GMT

Hi OwenAuger ,

If I use ALL() on top of the test table, the ranking will not be continuous when filtered.
When I use ALLSELECTED, Column2 and Column3 affect the ranking.
And D_TEST is a dimension table, so not all names will have data when filtered. As a result, I’m not getting continuous ranking. How can I overcome this scenario?

Thanks for your Help !

Re: Avoid Context When Ranking

OwenAuger — Wed, 21 Aug 2024 01:20:12 GMT

Understood, thanks for clarifying Anonymous 🙂

If we call the relevant fact table FactTable, you could write something like this:

RANK = IF ( NOT ISEMPTY ( FactTable ), VAR Names = CALCULATETABLE ( SUMMARIZE ( FactTable, D_TEST[NAME] ), ALLSELECTED () ) RETURN RANK ( DENSE, Names ) )

This should return a "dense" rank based solely on D_TEST[NAME]. The ranking is relative to the list of NAME values for which the FactTable is nonempty in the ALLSELECTED() context.

It will only return a rank when FactTable is nonempty.

Does this work for you?

Re: Avoid Context When Ranking

Anonymous — Mon, 26 Aug 2024 10:51:27 GMT

I'm glad the formula worked, Thank You !