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

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.