Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

RankX Ranking Ties

Hello, Can someone help with how to break ties? I have the below dax forumla creating a ranking column in my table using item code that thens ranks by the earliest date. I tried adding dense to th...
DAX
ranking
Ranking Ties
RANKX
Ties
  • tamerj1's avatar
    tamerj1
    3 years ago

    Hi Anonymous 

    Syntax error. You need to have thee commas before "Dense" not only one. However, this won't help to break the ties. There must be other columns that can be used to break the tie but simplist method is to create an index column using power query then your ranking formula would be

    Rtotal =
    RANKX (
    CALCULATETABLE (
    PO_PurchaseOrderDetail,
    ALLEXCEPT ( PO_PurchaseOrderDetail, PO_PurchaseOrderDetail[ItemCode] )
    ),
    PO_PurchaseOrderDetail[Index],
    ,
    asc,
    DENSE
    )

     

tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
3 years ago

Hi Anonymous 

Syntax error. You need to have thee commas before "Dense" not only one. However, this won't help to break the ties. There must be other columns that can be used to break the tie but simplist method is to create an index column using power query then your ranking formula would be

Rtotal =
RANKX (
CALCULATETABLE (
PO_PurchaseOrderDetail,
ALLEXCEPT ( PO_PurchaseOrderDetail, PO_PurchaseOrderDetail[ItemCode] )
),
PO_PurchaseOrderDetail[Index],
,
asc,
DENSE
)

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Thank you! This worked perfectly, I guess I just wasnt considering how the order entry table would already be in order by date since when a new one is entered its always the most recent entry.