Calculated column to show duration of unique ID

[DRossi]

Hello,

I need some help calculating a duration of a job ID only for one unique ID.

Current

This is what I have currently. Each ID returns a duration in the calculated column.

ID	Start time	End time	Duration
111	08:00:00	09:30:00	01:30:00
111	08:00:00	09:30:00	01:30:00
234	10:30:00	11:00:00	00:30:00
234	10:30:00	11:00:00	00:30:00
234	10:30:00	11:00:00	00:30:00
234	10:30:00	11:00:00	00:30:00

Required

This is what is requred. Each unique ID returns only one duration in the calculated column.

ID	Start time	End time	Duration
111	08:00:00	09:30:00	01:30:00
111	08:00:00	09:30:00
234	10:30:00	11:00:00	00:30:00
234	10:30:00	11:00:00
234	10:30:00	11:00:00
234	10:30:00	11:00:00

[Anonymous · Accepted solution]

Hi @DRossi ,

First, create a index column in the Power Query.

Second, create the calculated column.

Duration = 
VAR tt =
    RANKX (
        FILTER ( 'Table', 'Table'[ID] = EARLIER ( 'Table'[ID] ) ),
        [Index],
        ,
        ASC,
        DENSE
    )
RETURN
    IF ( tt = 1, [End time] - [Start time] )

You can check more details from here.

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.

[DRossi]

Smashed it @Anonymous  thanks so much.

[Tahreem24]

@DRossi ,

If you ingest your data into power BI so it will automatically show only unique records. Just create Calculated Column like below:

Diff = Table[End time]-Table[Start time]

[DRossi]

Hi @Tahreem24 , Unfortuantely it isn't as simple as that. The other rows and columns within the ID hold important information that I haven't shown.

I did attempt using this from a similar thread but struggled to modify:

Solved: Indexing rows by a distinct values and then gettin... - Microsoft Power BI Community

[Tahreem24]

@DRossi ,

I provided the solution based on the info you have shared. You should have shared in your post regarding more fields as a dummy entry.

[DRossi]

Apologies for that @Tahreem24