Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Serial Number for union table duplicates

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

UniqueidBLMStopStoreDate of plan
AA1s1AA11s109/21/2020 10:00
AA1s2AA12s209/22/2020 11:00
AA1s2AA12s209/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 

UniqueidBLMStopStoreDate of plan
AA1s1AA11s109/21/2020 10:00
AA1s2AA12s209/22/2020 11:00
AA1s2AA13s209/22/2020 11:00


Ive tried both dense and skip option in rankx

Thanks for your help in advance

 

Need Help
Tips and Tricks
Tutorial Requests
  • Fowmy's avatar
    Fowmy
    5 years ago

    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

     






4 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      Thanks a lot it worked 🙂

       

  • TomMartens's avatar
    TomMartens
    Super User
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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