Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi all, i'm new to power bi and i'm facing this problem:
I have 2 tables, one is called "alarm table" and have information about all alarms (start time of the alarm, end time of the alarm and alarm id) and the other table is called "shift alarm", that contains information about the shift (start time of the shift, end time of the shift and shift id). I want to create a new column in alarm table that will have the shift Id when the alarm happened.
In the example bellow, alarm 123 happened in shiftid 56596 and alarms 124 and 125 in the shiftid 56597
Alarm table
Alarm ID | starttime | endtime |
123 | 01/06/2021 06:53:00 | 01/06/2021 06:58:00 |
124 | 02/06/2021 07:00:00 | 01/06/2021 08:00:00 |
125 | 02/06/2021 09:00:00 | 02/06/2021 10:22:00 |
Shift Table
Shift ID | starttime | endtime |
56596 | 01/06/2021 06:45:00 | 01/06/2021 16:00:00 |
56597 | 02/06/2021 06:45:00 | 02/06/2021 16:00:00 |
56600 | 02/06/2021 17:10:00 | 03/06/2021 04:22:00 |
Thank you in advance
Solved! Go to Solution.
Hi @guilherme93silv ,
Try the following formula:
Shift ID =
CALCULATE(
MAX(Shift[Shift ID]),
FILTER(
Shift,
Shift[starttime] <= Alarm[starttime]
&& Shift[endtime] >= Alarm[endtime]
)
)
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
maybe you can try this
Column =
VAR _start=maxx(FILTER(Shift,'Shift'[Starttime]<=Alarm[Starttime]),Shift[Shift])
VAR _end=minx(FILTER(Shift,Shift[Endtime]>=Alarm[endtime]),Shift[Shift])
return if(_start=_end,_start)
Proud to be a Super User!
To identify Shift Id, should the StartTime(Alarm) between StartTime(Shift) and EndTime(Shift)? Or
StartTime(Alarm)> StartTime(Shift) and EndTime(Alarm) < EndTime(Shift)?
Please clarify.
Thanks!
Hi @Anonymous
It's the second option: StartTime(Alarm)> StartTime(Shift) and EndTime(Alarm) < EndTime(Shift)
Hi @guilherme93silv ,
Try the following formula:
Shift ID =
CALCULATE(
MAX(Shift[Shift ID]),
FILTER(
Shift,
Shift[starttime] <= Alarm[starttime]
&& Shift[endtime] >= Alarm[endtime]
)
)
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.