Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 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 too & I do not care about their rank. I just need a unique serial number column which I can use for cumulative sum.

 

The input table looks something like this. I need to add a Sr no. column without considering any rank or logic.

ProductMove-TypePiecesSr no.
4343MM21
3243Flex52
54543NM23
2431fLEX24

 

Thanks in advance for your help

Regards,

Samyak

Need Help
  • ToddChitt's avatar
    ToddChitt
    2 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:
     

     

     

     

10 Replies

  • ToddChitt's avatar
    ToddChitt
    Super User
    2 years ago

    You should still be able to use the RANK or RANKX funtion for this. The trick is to use the right PARTITION inside the function. Say you wanted to rank your users by Sales for each State. The ORDER BY clause would be Sales, and the PARTITION BY would be State. 

    I suggest you add a static column as: MyStaticColumn = "Foo"

    Then reference that column in the RANK function's PARTITION parameter. That puts ALL RECORDS in the table in the same partition, generating only one series of numbers starting at 1. 

    It is up to you to figure out the ORDER BY portion.

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi,

      I got your idea of identifying a right partition by adding a dummy column with Foo. However by using RANKX, I still have to rank it based on 'Sales' value. Now, if there are duplicate sales values, rankx assigns same rank to them. Thus, I do not have the required unique serial numbers. 
      So to better phrase my query, I want to assign unique serial numbers even if there are whole duplicate rows or values in 'Sales' column. As my requirement is not to rank rows but to calculate cumulative sum, I need an index column.

      • ToddChitt's avatar
        ToddChitt
        Super User
        2 years ago

        The trick is to add enough columns in the ORDER BY portion to guarantee that you have no duplicates. 

         

        I think the bigger question is this: What are you trying to accomplish in the end? What type of visual are you trying to present to your users? What story are you trying to tell with your data?

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Sure,

    I can give you a rough idea of how my calculated table looks like:

    Now my final goal is to calculate the 'Cumulative sum of pieces' column. As my calculated table doesn't have any order-date or index column, I need to add one for the cumulative sum. Thus, I want to order the 'Serial Number' column.
    Also I need to assign the serial number based on the values in 'Move priority' column. So all 'Move Priority'="Must Move" should be numbered first as 1,2.. & then 'Move Priority' ="Flexible" as 3,4,5 & finally 'Move Priority ="Not move" as 6. These serial numbers would be used for calculating the cumulative sum in same order.

    It would be really great if you can propose a simpler solution here?

    My solution was to just sort based on categories, all material belonging to "Must move" appear first & then flexible & then "Not Move". I would then just have to add an index column with unique serial numbers. But thats quite challenging too. I don't think adding enough columns to the table to make rows unique would make much sense because my original dataset has 60,000+ rows & the data changes on a daily basis.



  • Anonymous's avatar
    Anonymous
    Not applicable
    2 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
      2 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
        2 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!