Forum Discussion
Creating an Index column based on a calculated column and a regular column
Hi RH10 ,
How about this:
here the code:
Column = RANKX ( FILTER ( Table, Table[Year (Calculated)] = EARLIER ( Table[Year (Calculated)] ) && Table[Course name] = EARLIER ( Table[Course name] ) ), Table[Date], , ASC , DENSE )For reference:
Let me know if this helps! 🙂
By the way, I would still recommend to solve it in Power Query. You could even create your calculated column in PowerQuery, too. But since your requirement was to do it in DAX, I provided the solution in DAX as well./Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
Hi RH10 ,
Thanks for your reply.
The calculated year and Course name columns are used to do the grouping of your index. The date column is used to sort within the grouping figuring out which index comes first, second, third etc. If the order does not really matter, it should still work to use the Date column as the sorting column, shouldn't it? Alternatively, you could use another column in your dataset to sort upon. Do you have more columns?
/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/
Hello again tackytechtom ,
Using the date column led to repeated index values because of how messy the date column is, so I set the Table[Date] part to another column as you suggested and now it works. Thank you very much!