Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
97 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |