add a counter based on values in another column

[mhendel]

Hello,

I have a query-table which contains this : 

Employee	Phase
Mike	Phase 1
Mike	Phase 3
Mike	Phase 4
Mike	Phase 6
Mike	Phase 8
Mike	Phase 9
Patrick	Phase 1
Patrick	Phase 2
Patrick	Phase 5
Patrick	Phase 4
Sophia	Phase 3
Sophia	Phase 5
Sophia	Phase 7
Sophia	Phase 8
Annie	Phase 2
Annie	Phase 3
Annie	Pahse 5
Annie	Phase 6
Annie	Phase 7
Annie	phase 8

I would like to add a column which is a sort of counter : 

Employee	Phase	Counter
Mike	Phase 1	1
Mike	Phase 3	2
Mike	Phase 4	3
Mike	Phase 6	4
Mike	Phase 8	5
Mike	Phase 9	6
Patrick	Phase 1	1
Patrick	Phase 2	2
Patrick	Phase 5	3
Patrick	Phase 4	4
Sophia	Phase 3	1
Sophia	Phase 5	2
Sophia	Phase 7	3
Sophia	Phase 8	4
Annie	Phase 2	1
Annie	Phase 3	2
Annie	Pahse 5	3
Annie	Phase 6	4
Annie	Phase 7	5
Annie	phase 8	6

For each row you have the same employee, it increments the counter by 1.  Otherwise, it begins again at 1.  I hope i am clear here ;-).

Can someone help me ?

Thanks,

Michael

[AllisonKennedy · Accepted solution]

Looks like you're wanting to do this within the current sort of your table? Careful as the sort order may not be as you expect, do you have a Date or Index column that determines the sort? If so you can use RANKX

Or you could get the count by using EARLIER, but you still need a way to tell Power BI which rows are first, second, third, etc.

If you don't have an index or ranking column, just add on in Power Query:
Home > Transform Data > Add Column > Index > Close & Apply

Then you can use EARLIER to add a column in DAX:
Column = COUNTROWS(FILTER(ALL(Table), Table[Employee]=EARLIER(Table[Employee]) && Table[Index] >=EARLIER(Table[Index]))

[Fowmy]

@mhendel 

Paste the below code in a blank Query in Power Query and check the steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc65CoAwEEXRX5GpbdwSLS0sBcEyWAQJJLgFtfHvBUUhM9MeHryrFDSLn7fLGIihs/owMMQKWjf9ECWUMko5JUGppFQ91Olzd+OETrGmrBasvkH95q3TKBxhwaHk8M2v19UZ1BRaFpq2/0u4E4zJwPz3O9w=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Grouped Rows" = Table.Group(#"Promoted Headers", {"Employee"}, {{"Count", each _, type table [Employee=nullable text, Phase=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Count], "Counter", 1, 1)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Employee", "Phase", "Counter"}, {"Employee", "Phase", "Counter"})
in
    #"Expanded Custom"

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[mhendel]

Thanks a lot, you've made my day!!