Using RANKX to determine production sequence

[hedonistclam]

Hey 🙂

I have a table of production order ID's, coil ID's, and date of completed production. I want to make a new column that within each PO ranks the coils in the order they were produced. I think I'm supposed to use the RANKX-function, but I'm not sure how to apply filters.

production_order	Coil	First complete date
127	426	26.08.2018 14:24
970	900	25.06.2018 03:37
970	911	25.06.2018 04:57
970	152	01.09.2018 15:41
969	523	10.09.2018 04:10
969	525	10.09.2018 04:36
740	373	29.09.2018 19:49
740	386	29.09.2018 21:26
740	387	29.09.2018 23:04

Here, I'm trying to make a new column that gives coil 900 rank 1, coil 911 rank 2, and coil 152 rank 3, and then starts over from rank 1 on the next PO.

[Zubair_Muhammad · Accepted solution]

@hedonistclam

Try this calculated column

Column =
RANKX (
    FILTER ( Table1, [production_order] = EARLIER ( [production_order] ) ),
    [First complete date],
    ,
    ASC,
    DENSE
)

[hedonistclam]

That worked. Thanks a lot! I've been sitting for hours trying to figure this out 🙂