Forum Discussion

hackfifi's avatar
hackfifi
Helper V
6 years ago
Solved

Add Index based on 2 x Columns

Hi - Just need some help to create the INDEX column. It should be a sequence number sorted on Period but resets when Project Changes. Happy to use a calculated column or adding it to the Power Query ...
Need Help
  • v-deddai1-msft's avatar
    v-deddai1-msft
    6 years ago

    Hi hackfifi ,

     

    Would you please try to use 

     

     

    Index = RANKX(filter('Table','Table'[Project]=earlier('Table'[Project])),'Table'[Period],,ASC,Dense)

     

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    Best Regards,

    Dedmon Dai

     

az38's avatar
az38
Community Champion
6 years ago

hi hackfifi 

try a RANKX() column

Index = 
    RANKX(
        FILTER(
                'Table',
                'Table'[Project]=EARLIER('Table'[Project])
                ),
        'Table'[Period],,
        ASC
        )
hackfifi's avatar
hackfifi
Helper V
6 years ago

Hi az38  - Thanks for respondng. but it did not work. I filtered on 1 Project, But the index column is giving me random values

Index = RANKX(filter('Table','Table'[Project]=earlier('Table'[Project])),'Table'[Period],,ASC)

 

 

  • az38's avatar
    az38
    Community Champion
    6 years ago

    hackfifi 

    yes, you need to define tie-break rule for the one date - one project rows

    • hackfifi's avatar
      hackfifi
      Helper V
      6 years ago

      So the tie break rule is when the project changes, the index resets. So what am i doing wrong? 🙂

      • v-deddai1-msft's avatar
        v-deddai1-msft
        Community Support
        6 years ago

        Hi hackfifi ,

         

        Would you please try to use 

         

         

        Index = RANKX(filter('Table','Table'[Project]=earlier('Table'[Project])),'Table'[Period],,ASC,Dense)

         

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

         

        Best Regards,

        Dedmon Dai