Rank groups within unique IDs and by date

[jimminy]

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!

[Anonymous · Accepted solution]

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.           

[jimminy]

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?

[jimminy]

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

[Jihwan_Kim]

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] )
    )