Forum Discussion
Creating calculated index column in DAX
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.
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
2 Replies
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
