Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Avoid Context When Ranking

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,...
"DAX "
"DAX help"
"Need Help"
  • OwenAuger's avatar
    OwenAuger
    1 year ago

    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?

OwenAuger's avatar
OwenAuger
Super User
1 year ago

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's avatar
Anonymous
Not applicable
1 year ago

I'm glad the formula worked, Thank You !