Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

INDEX WITH 2 CONDITIONS

I have a table with REF & DATES clumns

I want to index my column REF in Power Query based on DATES one to end-up with below result (INDEX)

Dont care if with DAX or M language

REF          DATES            INDEX

A             01.01.2021       1 

B             06.03.2021       1

C             12.04.2021       1

A             15.01.2021       2

A             28.01.2021       3

C             14.04.2021       2

Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    Anonymous , Create a new column in DAX

    countx(filter(Table, Table[REF] = earlier(Table[REF]) && [dates] <= earlier([Dates])),[Dates])

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , Create a new column in DAX

    countx(filter(Table, Table[REF] = earlier(Table[REF]) && [dates] <= earlier([Dates])),[Dates])

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thank you, this worked well

  • serpiva64's avatar
    serpiva64
    Solution Sage
    4 years ago

    Hi, from your table

    You obtain this 

     

    by applying these steps

    You group by REF

    then you sort for DATES

    Add index

    remove all columns except Custom.1

    Expand and change type.

    That's done

    If this post is useful to help you to solve your issue consider giving the post a thumbs up 

     and accepting it as a solution !

     

     

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thanks dear

      I used Amitchandak solution wich worked well

      Is replying to my problem better because i can use it in desktop directly (On a union table) no need to go to power query

      I will use your solution on a different context

      Thanks