Forum Discussion
Rank groups within unique IDs and by date
Hi community!
I have the current example data:
| ticket_id | start_date_time_local | Service Team |
| 11 | 23/05/2023 11.42 | Team 1 |
| 11 | 23/05/2023 11.46 | Team 1 |
| 11 | 23/05/2023 11.47 | Team 2 |
| 11 | 23/05/2023 11.47 | Team 3 |
| 22 | 24/05/2023 15.16 | Team 5 |
| 22 | 25/05/2023 14.01 | Team 5 |
| 22 | 07/06/2023 05.19 | Team 2 |
| 22 | 03/07/2023 15.59 | Team 2 |
| 22 | 04/07/2023 12.25 | Team 4 |
| 22 | 04/07/2023 12.25 | Team 1 |
| 22 | 04/07/2023 14.47 | Team 1 |
And am trying to create a ranked column with the below output:
| ticket_id | start_date_time_local | Service Team | Rank |
| 11 | 23/05/2023 11.42 | Team 1 | 1 |
| 11 | 23/05/2023 11.46 | Team 1 | 1 |
| 11 | 23/05/2023 11.47 | Team 2 | 2 |
| 11 | 23/05/2023 11.47 | Team 3 | 3 |
| 22 | 24/05/2023 15.16 | Team 5 | 1 |
| 22 | 25/05/2023 14.01 | Team 5 | 1 |
| 22 | 07/06/2023 05.19 | Team 2 | 2 |
| 22 | 03/07/2023 15.59 | Team 2 | 2 |
| 22 | 04/07/2023 12.25 | Team 4 | 3 |
| 22 | 04/07/2023 12.25 | Team 1 | 4 |
| 22 | 04/07/2023 14.47 | Team 1 | 4 |
Thanks!
Hi jimminy ,
Here's my solution.
1.Create an index column in Power Query.
2.Go back to Power BI Desktop and create two calculated columns.
Column = IF( 'Table'[Index]= MINX( FILTER(ALL('Table'),'Table'[ticket_id]=EARLIER('Table'[ticket_id])&&'Table'[Service Team]=EARLIER('Table'[Service Team])),[Index]),1,0)Final Rank = SUMX( FILTER(ALL('Table'), 'Table'[ticket_id]=EARLIER('Table'[ticket_id])&&'Table'[Index]<=EARLIER('Table'[Index])),[Column])Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
- Jihwan_Kim
Super User
Hi,
I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.
It is for creating a new column.
Team Rank CC = VAR _t = GROUPBY ( Data, Data[ticket_id], Data[Service Team], "@startdatetime", MAXX ( CURRENTGROUP (), Data[start_date_time_local] ) ) RETURN RANK ( SKIP, _t, ORDERBY ( [@startdatetime], ASC, Data[Service Team], ASC ), PARTITIONBY ( Data[ticket_id] ) ) Thanks for taking the time, and this solution is what I'm looking for. Although when I use the above code I don't get exactly the right result. For example the result for a ticket using this code is shown below:
ticket_id 7777777 7777777 7777777 7777777 7777777 7777777 7777777 7777777 7777777 7777777 7777777 7777777 7777777 start_date_time_local 23/05/2023 11.42.58 23/05/2023 11.46.28 23/05/2023 11.47.14 23/05/2023 11.47.45 24/05/2023 14.46.47 24/05/2023 15.16.44 24/05/2023 15.16.56 25/05/2023 14.01.43 07/06/2023 05.19.03 03/07/2023 15.59.57 04/07/2023 12.25.03 04/07/2023 12.25.33 04/07/2023 14.47.46 service_and_support_team_id Team1 Team1 Team1 Team2 Team2 Team2 Team2 Team2 Team1 Team1 Team2 Team2 Team Rank CC 2 2 2 3 3 1 3 3 3 2 2 3 3 Where as the result I am looking for would be this:
ticket_id 7777777 7777777 7777777 7777777 7777777 7777777 7777777 7777777 7777777 7777777 7777777 7777777 7777777 start_date_time_local 23/05/2023 11.42.58 23/05/2023 11.46.28 23/05/2023 11.47.14 23/05/2023 11.47.45 24/05/2023 14.46.47 24/05/2023 15.16.44 24/05/2023 15.16.56 25/05/2023 14.01.43 07/06/2023 05.19.03 03/07/2023 15.59.57 04/07/2023 12.25.03 04/07/2023 12.25.33 04/07/2023 14.47.46 service_and_support_team_id Team1 Team1 Team1 Team2 Team2 Team2 Team2 Team2 Team1 Team1 Team2 Team2 Team Rank CC 1 1 1 2 2 3 4 4 4 5 5 6 6 Hi jimminy ,
Here's my solution.
1.Create an index column in Power Query.
2.Go back to Power BI Desktop and create two calculated columns.
Column = IF( 'Table'[Index]= MINX( FILTER(ALL('Table'),'Table'[ticket_id]=EARLIER('Table'[ticket_id])&&'Table'[Service Team]=EARLIER('Table'[Service Team])),[Index]),1,0)Final Rank = SUMX( FILTER(ALL('Table'), 'Table'[ticket_id]=EARLIER('Table'[ticket_id])&&'Table'[Index]<=EARLIER('Table'[Index])),[Column])Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, Thanks for the solution. However I run into an issue when the team repeats e.g. if it returns to team 1 after going to team 2. Do you know how to solve this?
