How do you enumurate rows at each change in level of a categorical variable?

daniellopez46

Refer to "Formula/Outcome" column which is my desired outcome. What formula can I use to create a sequence of numbers that restarts at each change in "composite key". In this example the "composite key" is ID and Category.

ID	Category	Subcategory	Formula/Outcome
101	Working Conditions	Office temperature control	1
101	Working Conditions	Takes long to fix things	2
101	Supervisor	Lack of career development	1
101	Supervisor	Availability	2
222	Compensation	Rate is behind industry average	1
222	Supervisor	Lack of career development	1
222	Supervisor	Availability	2

ms

This type formula should work if the table is sorted:

If( Row() > 1,

    If( :ID == Lag( :ID, 1 ) & :Category == Lag( :Category, 1 ),

        Lag( :Outcome, 1 ) + 1,

        1

    ),

    1

)

The below should also work (but independent of sorting).

Eval( Eval Expr( Col Number( Row(), Row() > Expr( Row() ), :ID, :Category ) ) )