Creating calculated index column in DAX

[mrothschild]

I've searched the forums and haven't found a clear answer to this.  I'm fairly new, but this worked for me and I thought could be helpful for others.

I have a calculated table, so couldn't create an index column using PowerQuery and DAX is new enough for me that I didn't want to delve into M or some other programming. 

First I created a column called UniqueID: 

UniqueID = 
FORMAT(VALUE(Table[Number]),"00#") & " - " & Table[Asset ID]  & " - " & FORMAT(Table[Period],"000#")

It really doesn't matter what you use, I'm just smooshing some columns together to ensure (a) the data is unique by row, and (b) it's sorted in the order I want it to be, which is what the FORMAT function above does.

Once that column has been created, the Index column is created as follows: 

Index = 
COUNT(Table[UniqueID]) - RANKX(Table,Table[UniqueID]) + 1

Now your UniqueID and Index columns should sort in the same order.  

[Norloff]

Thank you for this, could not figure out why this had not been solved easily, or find any other simple solutions. They should implement an INDEX function to just indexate a column freely.

[v-lili6-msft · Accepted solution]

hi, @mrothschild 

It's pleasant that your problem has been solved, and share your solution.

Usually, create an "Index" column in dax is the same logic as yours.

And it needs a unique column(eg. UniqueId), otherwise, for the same row will have the same index number.

You could also try to add multiple group rank column by different level then add then index column by the conditional

level1*1000+level2*100+level3*10...

It is like the way as you created a column called UniqueID: 

UniqueID = 
FORMAT(VALUE(Table[Number]),"00#") & " - " & Table[Asset ID]  & " - " & FORMAT(Table[Period],"000#")

And please close the topic and mark the relevant post as an answer. Thank you 

Best Regards,

Lin