cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
taisiya Regular Visitor
Regular Visitor

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

Hello jedis,

 

I need your help again.

 

I have the following dummy data:

 

TABLE 1
Parent idTask idDate AssignedDate Completed
88818/19/2018 9:208/19/2018 9:55
88828/19/2018 10:018/19/2018 10:05
88938/19/2018 10:308/19/2018 10:55
88948/19/2018 11:058/19/2018 11:08
    
    
TABLE 2 
Task idDepartment createdDepartment Assigned Department Completed
1AdministratorKitchen Kitchen
2AdministratorRoom  Service Room  Service
3GuestKitchen Kitchen
4GuestRoom  Service  Room  Service 
5SupervisorHousekeeping Housekeeping
6HousekeepingMaintenance Maintenance

 

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

 

But I need to do it only if Department Assigned/Department Completed are Kitchen and Room Service.

 

I hope I explained it well... Hope you can come up with some ideas. Thank you in advance!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support Team
Community Support Team

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

Hi @taisiya,

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:

1.PNG

You could also download the pbix file to have a view.

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Highlighted
Community Support Team
Community Support Team

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

Hi @taisiya,

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:

1.PNG

You could also download the pbix file to have a view.

 

Regards,

Daniel He

Community Support Team _ Daniel He
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
taisiya Regular Visitor
Regular Visitor

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

Thank you!!