Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It 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
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 |
Certainly! Let’s break down the steps to achieve your goal of joining the two tables and creating a calculated column using DAX:
Joining Tables:
Creating a Calculated Column:
Solution:
In Power BI Desktop, follow these steps:
a. Join Tables:
b. Create Calculated Column:
Combined Dates = CONCATENATEX( FILTER( MergedTable, MergedTable[Resource] = EARLIER(MergedTable[Resource]) ), MergedTable[Start Date] & " - " & MergedTable[End Date], ", " )
Result:
Here’s how the first few rows of your updated table might look:
Resource Available Start Available End Combined DatesPDXI 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.
User | Count |
---|---|
102 | |
91 | |
84 | |
77 | |
71 |
User | Count |
---|---|
113 | |
104 | |
100 | |
73 | |
65 |