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

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
3 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?