Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello jedis,
I need your help again.
I have the following dummy data:
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 |
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!
Solved! Go to Solution.
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:
You could also download the pbix file to have a view.
Regards,
Daniel He
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:
You could also download the pbix file to have a view.
Regards,
Daniel He
Thank you!!
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |