Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello!
I need help on this. I have two tables that I need to join and create a calculated columns using DAX
1. Bookable Resource Table
Data source set-up is Date/Time so we will just put a certain date fr this but only the time is used.
Resource | Available Start | Available End |
PDXI Support 1027 Juhoney br | 1/1/24 7:00 AM | 1/1/24 2:00 PM |
PDXI Support 1036 Ana Marie br | 1/1/24 8:00 AM | 1/1/24 2:00 PM |
2. Time Entry Table
Resource | Start Date | End Date |
PDXI Support 1027 Juhoney br | 2/19/24 7:05 AM | 2/19/24 8:20 AM |
PDXI Support 1027 Juhoney br | 2/19/24 8:24 AM | 2/19/24 9:27 AM |
PDXI Support 1027 Juhoney br | 2/19/24 9:29 AM | 2/19/24 11:34 AM |
PDXI Support 1027 Juhoney br | 2/19/24 11:36 AM | 2/19/24 12:18 PM |
PDXI Support 1027 Juhoney br | 2/19/24 12:20 PM | 2/19/24 2:45 PM |
PDXI Support 1027 Juhoney br | 2/20/24 7:16 AM | 2/20/24 7:34 AM |
PDXI Support 1027 Juhoney br | 2/20/24 7:36 AM | 2/20/24 8:16 AM |
PDXI Support 1027 Juhoney br | 2/20/24 8:17 AM | 2/20/24 9:27 AM |
PDXI Support 1027 Juhoney br | 2/20/24 10:01 AM | 2/20/24 12:04 PM |
PDXI Support 1027 Juhoney br | 2/20/24 12:06 PM | 2/20/24 2:22 PM |
PDXI Support 1027 Juhoney br | 2/21/24 7:04 AM | 2/21/24 7:29 AM |
PDXI Support 1027 Juhoney br | 2/21/24 7:30 AM | 2/21/24 8:10 AM |
PDXI Support 1027 Juhoney br | 2/21/24 8:12 AM | 2/21/24 10:24 AM |
PDXI Support 1027 Juhoney br | 2/21/24 10:25 AM | 2/21/24 1:14 PM |
PDXI Support 1027 Juhoney br | 2/21/24 1:17 PM | 2/21/24 2:45 PM |
PDXI Support 1027 Juhoney br | 2/22/24 7:06 AM | 2/22/24 7:26 AM |
PDXI Support 1027 Juhoney br | 2/22/24 7:29 AM | 2/22/24 8:20 AM |
PDXI Support 1027 Juhoney br | 2/22/24 8:22 AM | 2/22/24 10:29 AM |
PDXI Support 1027 Juhoney br | 2/22/24 10:31 AM | 2/22/24 2:20 PM |
PDXI Support 1036 Ana Marie br | 2/19/24 8:26 AM | 2/19/24 8:55 AM |
PDXI Support 1036 Ana Marie br | 2/19/24 8:55 AM | 2/19/24 9:00 AM |
PDXI Support 1036 Ana Marie br | 2/19/24 9:00 AM | 2/19/24 9:57 AM |
PDXI Support 1036 Ana Marie br | 2/19/24 10:06 AM | 2/19/24 10:39 AM |
PDXI Support 1036 Ana Marie br | 2/19/24 10:41 AM | 2/19/24 11:32 AM |
PDXI Support 1036 Ana Marie br | 2/19/24 11:35 AM | 2/19/24 2:44 PM |
PDXI Support 1036 Ana Marie br | 2/19/24 2:47 PM | 2/19/24 3:40 PM |
PDXI Support 1036 Ana Marie br | 2/20/24 8:05 AM | 2/20/24 9:03 AM |
PDXI Support 1036 Ana Marie br | 2/20/24 9:05 AM | 2/20/24 10:04 AM |
PDXI Support 1036 Ana Marie br | 2/20/24 10:07 AM | 2/20/24 12:02 PM |
PDXI Support 1036 Ana Marie br | 2/20/24 12:20 PM | 2/20/24 2:04 PM |
PDXI Support 1036 Ana Marie br | 2/20/24 3:10 PM | 2/20/24 5:32 PM |
PDXI Support 1036 Ana Marie br | 2/21/24 8:07 AM | 2/21/24 9:28 AM |
PDXI Support 1036 Ana Marie br | 2/21/24 9:30 AM | 2/21/24 12:16 PM |
PDXI Support 1036 Ana Marie br | 2/21/24 12:18 PM | 2/21/24 3:54 PM |
PDXI Support 1036 Ana Marie br | 2/22/24 8:12 AM | 2/22/24 9:18 AM |
PDXI Support 1036 Ana Marie br | 2/22/24 9:22 AM | 2/22/24 9:57 AM |
PDXI Support 1036 Ana Marie br | 2/22/24 10:00 AM | 2/22/24 11:27 AM |
PDXI Support 1036 Ana Marie br | 2/22/24 11:30 AM | 2/22/24 4:54 PM |
3. New Table
1. Resource: From the 2 tables
2. Date: From the time entry tabele|start date
3. Start time: This is the earliest start time for each date
4: End Time: This is the latest end time for each date
5. Available start time: From the Bookable Resource table|Available start
6. Available End Time: From the Bookable Resource table|Available end
7. Start/End Time Status: I am not sure what DAX formula would be used on this but here is the condition
>If Actual start time is before the available time then "Early Log-in
> If the Actual start time is between availabe time and 10min from available time (grace period) then "On-Time"(ex 7:00-7:10 time stamp is still "On time")
> If the Actual Start Time is beyond the grace period (Available time+10min) then "Late" (ex 2:11PM and beyond is late log-in)
Same scenario with end time status
Hope you could help me on this.
Resource | Date | Start Time | End Time | Available Start time | Available End Time | Start Time Status | End Time Status |
PDXI Support 1027 Juhoney br | February 19, 2024 | 7:05:15 AM | 2:45:29 PM | 7:00:00 AM | 2:00:00 PM | On-Time | Late Log-Out |
PDXI Support 1027 Juhoney br | February 20, 2024 | 7:16:12 AM | 2:22:53 PM | 7:00:00 AM | 2:00:00 PM | Late-Log-In | Late Log-Out |
PDXI Support 1027 Juhoney br | February 21, 2024 | 7:04:49 AM | 2:45:24 PM | 7:00:00 AM | 2:00:00 PM | On-Time | Late Log-Out |
PDXI Support 1027 Juhoney br | February 22, 2024 | 7:06:03 AM | 2:20:35 PM | 7:00:00 AM | 2:00:00 PM | On-Time | Late Log-Out |
PDXI Support 1036 Ana Marie br | February 19, 2024 | 8:26:43 AM | 3:40:36 PM | 8:00:00 AM | 2:00:00 PM | Late-Log-In | Late Log-Out |
PDXI Support 1036 Ana Marie br | February 20, 2024 | 8:05:51 AM | 5:32:54 PM | 8:00:00 AM | 2:00:00 PM | On-Time | Late Log-Out |
PDXI Support 1036 Ana Marie br | February 21, 2024 | 8:07:13 AM | 3:54:34 PM | 8:00:00 AM | 2:00:00 PM | On-Time | Late Log-Out |
PDXI Support 1036 Ana Marie br | February 22, 2024 | 8:12:49 AM | 4:54:34 PM | 8:00:00 AM | 2:00:00 PM | Late-Log-In | Late Log-Out |
Solved! Go to Solution.
Hi @juhoneyighot ,
Thanks for the reply from @lbendlin , please allow me to provide another insight:
Based on the two tables you provided, enter Power Query and select Merge Queries as New.
After the merge is completed, select the expand button in the upper right corner and select the corresponding column.
The table after data cleaning is as follows.
Create two calculated columns to calculate the status of start time and end time respectively.
Start Time Status =
VAR _hour =
HOUR ( 'New Table'[Start Date ] ) - HOUR ( 'New Table'[Available Start] )
VAR _minutes =
MINUTE ( 'New Table'[Start Date ] ) - MINUTE ( 'New Table'[Available Start] )
RETURN
IF (
HOUR('New Table'[Start Date ]) < HOUR('New Table'[Available Start]),
"Early Log-in",
IF (
AND (
'New Table'[Start Date ] >= 'New Table'[Available Start],
AND ( _hour = 0, _minutes <= 10 )
),
"On-Time",
"Late-Log-In"
)
)
End Time Status =
VAR _hour =
HOUR ( 'New Table'[End Date] ) - HOUR ( 'New Table'[Available End] )
VAR _minutes =
MINUTE ( 'New Table'[End Date] ) - MINUTE ( 'New Table'[Available End] )
RETURN
IF (
HOUR ( 'New Table'[End Date] ) < HOUR ( 'New Table'[Available End] ),
"Early Log-out",
IF (
AND (
'New Table'[End Date] >= 'New Table'[Available End],
AND ( _hour = 0, _minutes <= 10 )
),
"On-Time",
"Late Log-Out"
)
)
Drag the required fields to the report page for display. The page effect is as follows:
pbix file is attached.
If you have any further questions please feel free to contact me.
Best Regards,
Yang
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
Start Time Status = SWITCH(TRUE(),
[Start Delta]>10,"Late Log-in",
[Start Delta]<0,"Early Log-in",
"On-Time")
Hi @juhoneyighot ,
Thanks for the reply from @lbendlin , please allow me to provide another insight:
Based on the two tables you provided, enter Power Query and select Merge Queries as New.
After the merge is completed, select the expand button in the upper right corner and select the corresponding column.
The table after data cleaning is as follows.
Create two calculated columns to calculate the status of start time and end time respectively.
Start Time Status =
VAR _hour =
HOUR ( 'New Table'[Start Date ] ) - HOUR ( 'New Table'[Available Start] )
VAR _minutes =
MINUTE ( 'New Table'[Start Date ] ) - MINUTE ( 'New Table'[Available Start] )
RETURN
IF (
HOUR('New Table'[Start Date ]) < HOUR('New Table'[Available Start]),
"Early Log-in",
IF (
AND (
'New Table'[Start Date ] >= 'New Table'[Available Start],
AND ( _hour = 0, _minutes <= 10 )
),
"On-Time",
"Late-Log-In"
)
)
End Time Status =
VAR _hour =
HOUR ( 'New Table'[End Date] ) - HOUR ( 'New Table'[Available End] )
VAR _minutes =
MINUTE ( 'New Table'[End Date] ) - MINUTE ( 'New Table'[Available End] )
RETURN
IF (
HOUR ( 'New Table'[End Date] ) < HOUR ( 'New Table'[Available End] ),
"Early Log-out",
IF (
AND (
'New Table'[End Date] >= 'New Table'[Available End],
AND ( _hour = 0, _minutes <= 10 )
),
"On-Time",
"Late Log-Out"
)
)
Drag the required fields to the report page for display. The page effect is as follows:
pbix file is attached.
If you have any further questions please feel free to contact me.
Best Regards,
Yang
Community Support Team
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!
Applied the insights and it looks like this.
This is a simplified one.
I've tried to get the Start /End Time Status
Use this DAX measure:
Start Time Status = SWITCH(
TRUE,
[Start Delta]>10="Late Log-in",
[Start Delta]<0="Early Log-in",
[Start Delta]>=0 && [Start Delta]<=10,"On-Time",
""
)
However, as Ive added this on the visual error occurs
Or I'm thinking for a calculated column but I am not sure what formula I could use since there are multiple conditions. I hope you could help me on this.
Thank you
Start Time Status = SWITCH(TRUE(),
[Start Delta]>10,"Late Log-in",
[Start Delta]<0,"Early Log-in",
"On-Time")
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
69 | |
35 | |
21 | |
18 | |
15 |
User | Count |
---|---|
126 | |
32 | |
28 | |
24 | |
24 |