Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Abishek
Helper I
Helper I

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, 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:

NAMECOLUMN2COLUMN3RANK - TEST
assistant1AJul-2420
assistant1BJul-2420
assistant1BAug-2420
assistant2AJul-2462
assistant2BJun-2462
assistant2BJul-2462
assistant2BAug-2462
assistant2CJul-2462
assistant3AJul-24302
assistant3BJul-24302
assistant3BAug-24302
1 ACCEPTED SOLUTION

Understood, thanks for clarifying @Abishek  🙂

 

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?


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
LinkedIn

View solution in original post

4 REPLIES 4
OwenAuger
Super User
Super User

Hi @Abishek 

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?


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
LinkedIn

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 !


Understood, thanks for clarifying @Abishek  🙂

 

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?


Owen Auger
Did I answer your question? Mark my post as a solution!
Blog
LinkedIn

I'm glad the formula worked, Thank You !

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.