Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
v-danhe-msft
Employee
Employee

Hi @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:

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.

View solution in original post

2 REPLIES 2
v-danhe-msft
Employee
Employee

Hi @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:

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.
Anonymous
Not applicable

Thank you!!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.