Generating number sequence based on condition

[SJ25]

Hello All,

I have a table with 2 columns Order number and Steps, and I am want to add a third column that will give me a sequence number based on the order number. An Example of what I want is shown in the table below:

Order Number	Steps	Sequence Number
1111	A	1
1111	B	2
1111	C	3
1112	B	1
1112	D	2

Any suggestions on how I can accomplish this or what the DAX formula should look like?

Thanks

[sanimesa]

@SJ25You can do this in DAX.

Please define the below column and two measures in this sequence:

Steps num = 26 - FIND(RankFromSequence[Steps], "ABCDEFGHIJKLMNOPQRSTUVWXYZ")

Total Steps Num = SUM(RankFromSequence[Steps num])

Sequence Number Measure = RANKX(ALLEXCEPT(RankFromSequence, RankFromSequence[Order Number]), [Total Steps Num])

Then use the Sequence Number Measure in your visual.

[amitchandak]

@SJ25 , Rank will check column rank

For Rank Refer these links

https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale

[SJ25 · Accepted solution]

@amitchandak This link: https://radacad.com/create-row-number-for-each-group-in-power-bi-using-power-query gave me exactly what I was looking for.

[amitchandak]

@SJ25 , seem like there are few steps, but fine as long as it solves the problem.