Serial Number for union table duplicates

[Anonymous]

Hello Folks,
How are you guys ? Im able to generate the column "Stop" for the table as shown below 

Uniqueid	BLM	Stop	Store	Date of plan
AA1s1	AA1	1	s1	09/21/2020 10:00
AA1s2	AA1	2	s2	09/22/2020 11:00
AA1s2	AA1	2	s2	09/22/2020 11:00

with the followiung dax 

Stop = (
                RANKX(
                     FILTER(Table1,Table1[BLM] = EARLIER(Table1[BLM]),
                        Table1[Date of Planl],,ASC,Dense))

Im aware that 3rd record is a duplicate of row 2 but i want a new number to be assigned by "DAX" ( no power query please since it is a union table) as below 

Uniqueid	BLM	Stop	Store	Date of plan
AA1s1	AA1	1	s1	09/21/2020 10:00
AA1s2	AA1	2	s2	09/22/2020 11:00
AA1s2	AA1	3	s2	09/22/2020 11:00

Ive tried both dense and skip option in rankx

Thanks for your help in advance

[Fowmy · Accepted solution]

@Anonymous 

You need to add a helper column to your table like:

RankSupport = [Date of plan] + RAND()

Then add the STOP column with this code:

Stop = 
RANKX(
    FILTER(Table5,Table5[BLM] = EARLIER(Table5[BLM])),
    Table5[RankSupport],,ASC,Dense
)

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[Anonymous]

Thanks a lot it worked 🙂

[TomMartens]

Hey @Anonymous ,

I used this DAX statement for a calculated column:

Column = RANKX(
    ALL('Table'[Uniqueid] , 'Table'[BLM] , 'Table'[Store] , 'Table'[Date of plan])
    , CALCULATE(MAX('Table'[Date of plan]))
    ,
    , ASC
)

The result will look like this:

Hopefully, this is what you are looking for.

Regards,
Tom

[Anonymous]

Hi @TomMartens  im super sorry ...the date in row 2 and row 3 has to be the same i changed it now.. Also i tired your approach and it throws error "circular dependency" is detected