Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Rank duplicate ID based on date

Good day! Im facing a small issue but would help a lot when solved. My dataset shows the following:

JobPU Date
101/01/23
101/02/23
101/03/23
201/01/23
301/01/23
3

01/02/23

 

However I need a new column (in query editor) to rank them like the following:

JobPU Date Step
101/01/23 1
101/02/23 2
101/03/23 3
201/01/23 1
301/01/23 1
301/02/23 2



Would help me out a lot if someone has the solution for me! Table is called 'Jobs Ranked'
Thank you!

Need Help
Show and Tell
Tips and Tricks
  • Ritaf1983's avatar
    Ritaf1983
    2 years ago

    Hi Anonymous 

    Add a calculated column with the dax code :

    Ranking =
    RANKX(
        FILTER(
            ALL('Table (2)'),
            'Table (2)'[Job] = EARLIER('Table (2)'[Job])
        ),
        'Table (2)'[PU Date],
        ,
        ASC
    )

    If this post helps, then please consider Accepting it as the solution to help the other

    members find it more quickly

6 Replies

  • Ritaf1983's avatar
    Ritaf1983
    Super User
    2 years ago

    Hi Anonymous 

    Add a calculated column with the dax code :

    Ranking =
    RANKX(
        FILTER(
            ALL('Table (2)'),
            'Table (2)'[Job] = EARLIER('Table (2)'[Job])
        ),
        'Table (2)'[PU Date],
        ,
        ASC
    )

    If this post helps, then please consider Accepting it as the solution to help the other

    members find it more quickly

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hello! Thank you but this is not the option I'm looking for. This works for table view in the report but wom't show the data in query editor. I need to have it in Query editor to go further with new steps. Do you know how to achieve this?