Create Sequence Number using DAX

[dp106]

Hi,

I need create Sequence number using DAX function.  Here is an example

DEPT NO   EMP NO   SEQNO

10               100          3

10               200          2

10               300          1

20               400          3

20               500          2

20               600          1

I tried using Rankx  but could not get it right.

Thanks

[v-yulgu-msft · Accepted solution]

Hi @dp106,

For calculated column,

SEQNO =
RANKX (
    FILTER ( Table8, Table8[DEPT NO] = EARLIER ( Table8[DEPT NO] ) ),
    Table8[EMP NO],
    ,
    DESC,
    DENSE
)

For measure,

SEQNO measure =
RANKX (
    ALLEXCEPT ( Table8, Table8[DEPT NO] ),
    CALCULATE ( SUM ( Table8[EMP NO] ) ),
    ,
    DESC,
    DENSE
)

Best regards,

Yuliana Gu

[AlB]

Hi @dp106

Do you want to follow the order of 'EMP NO'?

Show your code for RANKX and we can have a look