How to join tables and add calculated columns using DAX

[juhoneyighot]

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 
  

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

[AnalyticsWizard]

Certainly! Let’s break down the steps to achieve your goal of joining the two tables and creating a calculated column using DAX:

1. Joining Tables:

   • You have two tables: Bookable Resource Table and Time Entry Table.
   • We’ll join them based on the common column Resource.

2. Creating a Calculated Column:

   • You want to create a calculated column that combines the Start Date and End Date from the Time Entry Table for each resource.

3. Solution:

   • In Power BI Desktop, follow these steps:

     a. Join Tables:

     • Load both tables into Power BI.
     • Open the Query Editor (Edit Queries).
     • Select the Bookable Resource Table.
     • Click the Merge Queries button in the Home tab.
     • Choose the Time Entry Table as the second table to merge.
     • Select the common column Resource.
     • Choose Left Outer Join (since you want to keep all rows from the resource table).
     • Click OK to create a new table with combined data.

     b. Create Calculated Column:

     • In the Model View, select the merged table (let’s call it MergedTable).
     • Go to Modeling ➡️ New Column.
     • Enter the following DAX formula:

       Combined Dates = CONCATENATEX(
           FILTER(
               MergedTable,
               MergedTable[Resource] = EARLIER(MergedTable[Resource])
           ),
           MergedTable[Start Date] & " - " & MergedTable[End Date],
           ", "
       )

     • This formula concatenates the Start Date and End Date for each resource, separated by a comma.

4. Result:

   • Your new calculated column Combined Dates will contain the combined date ranges for each resource.

Here’s how the first few rows of your updated table might look:

Resource Available Start Available End Combined Dates

PDXI Support 1027 Juhoney br	1/1/24 7:00 AM	1/1/24 2:00 PM	2/19/24 7:05 AM - 2/19/24 8:20 AM, …
PDXI Support 1036 Ana Marie br	1/1/24 8:00 AM	1/1/24 2:00 PM	2/19/24 8:26 AM - 2/19/24 8:55 AM, …

Remember to adjust the table and column names according to your actual data.