Forum Discussion
Add index using DAX
HI, I need help to create a sequence number as the photo shows, notice that I have the color duplicate, that is correct, every color belong to a different Style, I need a DAX code to built the sequence and used it later in a calculation Thanks
You can do this in the query editor as is, but to do it with a DAX column you need another column that can be used to sort the color column (e.g., a date, a full table index).
Pat
HiRigolleto,
If you want to use DAX to meet your need, you have to create a new column and help you to sort it.
In my demo, I've created a column named “Group”.
Then you can try measure like this:
Index = RANKX(FILTER(ALL('Table'),'Table'[Color]=MAX('Table'[Color])),CALCULATE(MAX('Table'[Group])))
As a workaround:
It can also be achieved in the query editor, you can group on “Color” with operation "All Rows" ,then adjust the generated to have an Index added to the nested tables while grouping.
For details, please refer to the following video : NestedIndex in PowerBI
Here is the demo, please try it.
Hope it helps.
Best Regards,
Caitlyn Yan
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
You can do this in the query editor as is, but to do it with a DAX column you need another column that can be used to sort the color column (e.g., a date, a full table index).
Pat
HiRigolleto,
If you want to use DAX to meet your need, you have to create a new column and help you to sort it.
In my demo, I've created a column named “Group”.
Then you can try measure like this:
Index = RANKX(FILTER(ALL('Table'),'Table'[Color]=MAX('Table'[Color])),CALCULATE(MAX('Table'[Group])))
As a workaround:
It can also be achieved in the query editor, you can group on “Color” with operation "All Rows" ,then adjust the generated to have an Index added to the nested tables while grouping.
For details, please refer to the following video : NestedIndex in PowerBI
Here is the demo, please try it.
Hope it helps.
Best Regards,
Caitlyn Yan
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.