Forum Discussion
Solved
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 ...
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.
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
| start_date_time_local
| service_and_support_team_id
| Team Rank CC
|
Where as the result I am looking for would be this:
ticket_id
| start_date_time_local
| service_and_support_team_id
| Team Rank CC
|