Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Adding a simple index column using dax

Hi,   I just want to add an index column to a calculated table in Power BI. Every solution that I have seen uses RANKX to compare rows & assign a rank to them. But, in my case I have duplicate rows...
Need Help
  • ToddChitt's avatar
    ToddChitt
    3 years ago

    So if I understand you correctly, the rows marked "Must Move" should be ranked as more important (i.e. lower index) than "Flexible", with "Not Move" being less important. 

    If you have this Column already:

    Foo = "Foo"
    And this column:
    MyPriority = IF ( [Move priority] = "Must Move", 1, IF ( [Move priority] = "Flexible", 2, 3))
     
    Then you can add this column:
    My Rank = RANK(DENSE,,ORDERBY('Table'[MyPriority], ASC, 'Table'[MATNR], ASC),DEFAULT,PARTITIONBY('Table'[Foo]),)
     
    Result:
     

     

     

     

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Also my visual would be a table showing relevant columns. The end goal is to partition this material list & assign each to specific dates. So materials with cumulative sum(CS)<500 would be assigned to a specific order date, materials with 500<CS<=1000 to another date... & so on

  • ToddChitt's avatar
    ToddChitt
    Super User
    3 years ago

    So if I understand you correctly, the rows marked "Must Move" should be ranked as more important (i.e. lower index) than "Flexible", with "Not Move" being less important. 

    If you have this Column already:

    Foo = "Foo"
    And this column:
    MyPriority = IF ( [Move priority] = "Must Move", 1, IF ( [Move priority] = "Flexible", 2, 3))
     
    Then you can add this column:
    My Rank = RANK(DENSE,,ORDERBY('Table'[MyPriority], ASC, 'Table'[MATNR], ASC),DEFAULT,PARTITIONBY('Table'[Foo]),)
     
    Result:
     

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi,

      This solution worked wonderfully. I was able to assign unqiue serial numbers to all the rows. Thanks a lot!
      Can I also trouble you with a follow-up questions. Now, when I try to calculate the cumulative sum using the formula below, it gives me the sum of all pieces (16) in all rows

       

      Pieces_cumulative = CALCULATE(SUM('TABLE'[pieces]), FILTER( ALL('table'),'TABLE'[Serial Number]<=MAX('TABLE'[Serial Number])))
      Could you please point out what am I doing wrong here?
      Thanks in advance!
      • ToddChitt's avatar
        ToddChitt
        Super User
        3 years ago

        If the solution worked, accepting it as such lets other know where to find answers. Plus it really makes me feel good and I like to feel good. 🙂

        For your cumulative sum issue, try this post:

        Solved: Cumulative sum in DAX - Microsoft Fabric Community

        Your DAX looks right, Can you build a simple table visual with the following columns: [Serial Number], [pieces] ,[cumulative DAX measure].