Add index using DAX

[Rigolleto]

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

[mahoneypat · Accepted solution]

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

[Anonymous · Accepted solution]

Hi@Rigolleto, 

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.

[mahoneypat · Accepted solution]

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