Avoid Context When Ranking

[Anonymous]

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

[OwenAuger]

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?

[Anonymous]

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 !

[OwenAuger · Accepted solution]

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?

[Anonymous]

I'm glad the formula worked, Thank You !