Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi there
I have a fact table with a date time field and dimension table with the shift type.
I need to calculate, based on the shift being undertaken, whether an interaction occured within the shift hours or outside the shift hours.
The shift start and end times are columns in any table.
Table1(ShiftType) = "Afternoon"
Shift Times are 12:30pm to 11:30pm
Table1(ShiftType) = "Day"
Shift times are 08:30am to 16:30pm
I'm trying to calculate whether Table2(InteractionDateTime) has occured outside those hours for the relevant shift.
Any advice?
Solved! Go to Solution.
Hi @Anonymous,
Please follow the steps below.
1. Create two calculated columns in Table 1.
starttime = IF('Table1'[ShiftType]="Afternoon",TIME(12,30,00),IF('Table1'[ShiftType]="Day",TIME(8,30,00))) endtime = IF('Table1'[ShiftType]="Afternoon",TIME(23,30,00),IF('Table1'[ShiftType]="Day",TIME(16,30,00)))
2. Create the calculated column in Table 2.
Column 2 = IF ( AND ( TIMEVALUE ( 'Table2'[InteractionDateTime] ) >= TIMEVALUE ( RELATED ( Table1[starttime] ) ), TIMEVALUE ( 'Table2'[InteractionDateTime] ) <= TIMEVALUE ( RELATED ( 'Table1'[endtime] ) ) ), 1, 0 )
Here is the output.
More details, you could refer to the attacement below.
If you still need help, could you share your desired output so that we could help further on it?
Best Regards,
Cherry
Hi @Anonymous
would you be able to past a sample of your two tables?
thank you
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Yep sure @LivioLanzo
Table2
ID | InteractionDateTime |
P1 | 18/09/2018 22:40 |
P2 | 17/09/2018 15:20 |
P3 | 1/09/2018 3:40 |
P4 | 1/09/2018 22:20 |
P5 | 1/09/2018 8:00 |
P6 | 1/09/2018 17:30 |
Table1
ID | ShiftType |
P1 | Afternoon |
P2 | Day |
P3 | Afternoon |
P4 | Afternoon |
P5 | Afternoon |
P6 | Day |
Hi @Anonymous,
Please follow the steps below.
1. Create two calculated columns in Table 1.
starttime = IF('Table1'[ShiftType]="Afternoon",TIME(12,30,00),IF('Table1'[ShiftType]="Day",TIME(8,30,00))) endtime = IF('Table1'[ShiftType]="Afternoon",TIME(23,30,00),IF('Table1'[ShiftType]="Day",TIME(16,30,00)))
2. Create the calculated column in Table 2.
Column 2 = IF ( AND ( TIMEVALUE ( 'Table2'[InteractionDateTime] ) >= TIMEVALUE ( RELATED ( Table1[starttime] ) ), TIMEVALUE ( 'Table2'[InteractionDateTime] ) <= TIMEVALUE ( RELATED ( 'Table1'[endtime] ) ) ), 1, 0 )
Here is the output.
More details, you could refer to the attacement below.
If you still need help, could you share your desired output so that we could help further on it?
Best Regards,
Cherry
Worked really well. Thanks heaps!
User | Count |
---|---|
106 | |
86 | |
81 | |
73 | |
71 |
User | Count |
---|---|
112 | |
100 | |
98 | |
72 | |
66 |