topic Re: Join 2 table with additional calculated columns using dax in DAX Commands and Tips

Join 2 table with additional calculated columns using dax

juhoneyighot — Wed, 27 Mar 2024 18:26:49 GMT

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

Re: Join 2 table with additional calculated columns using dax

lbendlin — Thu, 28 Mar 2024 00:46:41 GMT

I would simplify the ask

See attached. Note the data model. No need for any programmatic joins.

Re: Join 2 table with additional calculated columns using dax

Anonymous — Thu, 28 Mar 2024 02:43:28 GMT

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!

Re: Join 2 table with additional calculated columns using dax

juhoneyighot — Thu, 28 Mar 2024 14:51:28 GMT

lbendlin

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

Re: Join 2 table with additional calculated columns using dax

lbendlin — Thu, 28 Mar 2024 15:25:37 GMT

Start Time Status = SWITCH(TRUE(),
[Start Delta]>10,"Late Log-in",
[Start Delta]<0,"Early Log-in",
"On-Time")