Forum Discussion
add a counter based on values in another column
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
- 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]))
3 Replies
- Fowmy
Super User
Anonymous
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 🙂
- AllisonKennedy
Community Champion
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]))
