topic Re: RankX Ranking Ties in DAX Commands and Tips

RankX Ranking Ties

Anonymous — Fri, 27 Jan 2023 16:51:37 GMT

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 the ties portion of the formula and I get this error

Ranking =

VAR __cd = PO_PurchaseOrderDetail[ItemCode]

RETURN

RANKX( FILTER( PO_PurchaseOrderDetail, PO_PurchaseOrderDetail[ItemCode] = __cd ),PO_PurchaseOrderDetail[PODATE])

Ranking error using Dense:

Rtotal =

VAR __po = PO_PurchaseOrderDetail[PurchaseOrderNo]

VAR __cd = PO_PurchaseOrderDetail[ItemCode]

RETURN

RANKX( FILTER( PO_PurchaseOrderDetail, PO_PurchaseOrderDetail[ItemCode] = __cd ),PO_PurchaseOrderDetail[PODATE], DENSE)

Results:

Expected Ressult:

Thanks so much in advance!

Re: RankX Ranking Ties

tamerj1 — Fri, 27 Jan 2023 17:07:17 GMT

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
)

Re: RankX Ranking Ties

Anonymous — Wed, 01 Feb 2023 14:45:11 GMT

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.