Forum Discussion

os_ca's avatar
os_ca
Frequent Visitor
8 years ago
Solved

How to rank Unique ID Per group

I tried to achive this but not successful. I like ranking Unique Person ID per Address ID. I used a number of solutions in this forum but they all do ranking per record. Address ID        Address   ...
Need Help
  • v-chuncz-msft's avatar
    v-chuncz-msft
    8 years ago

    os_ca,

     

    You may add a calculated column as follows.

    Column =
RANKX (
    FILTER ( Table1, Table1[Address ID] = EARLIER ( Table1[Address ID] ) ),
    Table1[PersonID],
    ,
    ASC,
    DENSE
)
v-chuncz-msft's avatar
v-chuncz-msft
Icon for Community Support rankCommunity Support
8 years ago

os_ca,

 

You may add a calculated column as follows.

Column =
RANKX (
    FILTER ( Table1, Table1[Address ID] = EARLIER ( Table1[Address ID] ) ),
    Table1[PersonID],
    ,
    ASC,
    DENSE
)
os_ca's avatar
os_ca
Frequent Visitor
8 years ago

Thanks. Your solution works but there is another issue i am facing. As soon as the list has got a filter, the filter is ignored for the ranking the list but is applied for displaying only.

eg.

  Person ID 301,400,500 are selected in a person filter. Then the list shows the below. Is there any workaround to fix the problem? Person 1 still be counted only for ranking process.

 

 address id     Person ID            Ranking                                   

  1                     301                             2              -->It should be ranking 1 now because person 300 is excluced in the filter.

  2                     400                             1

  2                     400                             1

  3                     500                             1 

  ......