Time difference between different rows and columns for tasks with the same parent id

Question

Hello jedis,&nbsp;I need your help again.&nbsp;I have the following dummy data:&nbsp;TABLE 1Parent idTask idDate&nbsp;AssignedDate Completed88818/19/2018 9:208/19/2018 9:5588828/19/2018&nbsp;10:018/19/2018 10:0588938/19/2018 10:308/19/2018 10:5588948/19/2018 11:058/19/2018 11:08&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;TABLE 2&nbsp;Task idDepartment createdDepartment Assigned&nbsp;Department Completed1AdministratorKitchen&nbsp;Kitchen2AdministratorRoom&nbsp; Service&nbsp;Room&nbsp; Service3GuestKitchen&nbsp;Kitchen4GuestRoom&nbsp; Service&nbsp;&nbsp;Room&nbsp; Service&nbsp;5SupervisorHousekeeping&nbsp;Housekeeping6HousekeepingMaintenance&nbsp;Maintenance&nbsp;What I need is to find reaction times, e.g. time differences in the following manner:If parent_ids are the same (e.g. parent_id = 888 for task_id_1 and task_id_2), then task_id_2 (date_assigned) - task_id_1 (date_completed) = reaction time for room service&nbsp;But I need to do it only if Department Assigned/Department Completed are Kitchen and Room Service.&nbsp;I hope I explained it well... Hope you can come up with some ideas. Thank you in advance!

v-danhe-msft · Accepted Answer

Hi&nbsp;Anonymous,
Based on my test, you could refer to below formula:
Create this measure:
Measure =
VAR M =
    CALCULATE (
        MAX ( 'Table1'[Date Completed] ),
        FILTER ( ALL ( 'Table1' ), 'Table1'[Parent id] = MAX ( 'Table1'[Parent id] ) ),
        FILTER (
            'Table2',
            'Table2'[Department Assigned] = "Kitchen"
                || 'Table2'[Department Assigned] = "Room Service"
                || 'Table2'[ Department Completed] = "Kitchen"
                || 'Table2'[ Department Completed] = "Room Service"
        )
    )
VAR N =
    CALCULATE (
        MIN ( 'Table1'[Date Assigned] ),
        FILTER ( ALL ( 'Table1' ), 'Table1'[Parent id] = MAX ( 'Table1'[Parent id] ) ),
        FILTER (
            'Table2',
            'Table2'[Department Assigned] = "Kitchen"
                || 'Table2'[Department Assigned] = "Room Service"
                || 'Table2'[ Department Completed] = "Kitchen"
                || 'Table2'[ Department Completed] = "Room Service"
        )
    )
RETURN
    IF ( M = MAX ( 'Table1'[Date Completed] ), DATEDIFF ( N, M, MINUTE ), 0 )

Result:

You could also download the pbix file to have a view.
&nbsp;
Regards,
Daniel He

Forum Discussion

Time difference between different rows and columns for tasks with the same parent id

2 Replies

Related content

Different schema into different destination

Page Level filter for different groups

Difference between different Staging lakehouses

Dynamic Calculation Different Period

1. How to add two different calculated columns of two different table

TABLE 1
Parent id	Task id	Date Assigned	Date Completed
888	1	8/19/2018 9:20	8/19/2018 9:55
888	2	8/19/2018 10:01	8/19/2018 10:05
889	3	8/19/2018 10:30	8/19/2018 10:55
889	4	8/19/2018 11:05	8/19/2018 11:08


TABLE 2
Task id	Department created	Department Assigned	Department Completed
1	Administrator	Kitchen	Kitchen
2	Administrator	Room Service	Room Service
3	Guest	Kitchen	Kitchen
4	Guest	Room Service	Room Service
5	Supervisor	Housekeeping	Housekeeping
6	Housekeeping	Maintenance	Maintenance