Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Rank - distinct values for ties

Hi all, 

 

I've got stuck trying to figure this out and was wandering if anyone could help guide me in the right direction.

 

I've got some below project data. The count column is a MEASURE that counts the occurrence of point with some other slicers in place on the report page. What I need is the rank measure that differentiates those rows that have same count and works with slicers. Any suggestions would be deeply appreciated.

Rank

 

Thanks,

 

EDIT: essentially what I am after is a machenism to distinguish ties. Instead of having all 1's for ties, I would like to have 1,2,3... Hope this helps.

Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    9 years ago

    Hi Anonymous,

     

    In Query Editor mode, please add an index column. Before adding [Index], please sort data based on [Project Name] in ascending order.

     

    Then, create rank measure as below: (In my test, the source table is named as 'Rank')

    Rank =
RANKX (
    FILTER ( ALLSELECTED ( 'Rank' ), 'Rank'[Points] = MAX ( 'Rank'[Points] ) ),
    LASTNONBLANK ( 'Rank'[Index], 1 ),
    ,
    1
)

    Best regards,
    Yuliana Gu

1 Reply

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi Anonymous,

     

    In Query Editor mode, please add an index column. Before adding [Index], please sort data based on [Project Name] in ascending order.

     

    Then, create rank measure as below: (In my test, the source table is named as 'Rank')

    Rank =
RANKX (
    FILTER ( ALLSELECTED ( 'Rank' ), 'Rank'[Points] = MAX ( 'Rank'[Points] ) ),
    LASTNONBLANK ( 'Rank'[Index], 1 ),
    ,
    1
)

    Best regards,
    Yuliana Gu