Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
juhoneyighot
Helper II
Helper II

Join 2 table with additional calculated columns using dax

‎03-27-2024 11:26 AM

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.

ResourceAvailable StartAvailable End
PDXI Support 1027 Juhoney br1/1/24 7:00 AM1/1/24 2:00 PM
PDXI Support 1036 Ana Marie br1/1/24 8:00 AM1/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 AM2/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    DateStart TimeEnd TimeAvailable Start timeAvailable End TimeStart Time StatusEnd Time Status
PDXI Support 1027 Juhoney br   February 19, 20247:05:15 AM2:45:29 PM7:00:00 AM 2:00:00 PMOn-TimeLate Log-Out
PDXI Support 1027 Juhoney br    February 20, 20247:16:12 AM2:22:53 PM7:00:00 AM 2:00:00 PMLate-Log-InLate Log-Out
PDXI Support 1027 Juhoney br   February 21, 20247:04:49 AM2:45:24 PM7:00:00 AM 2:00:00 PMOn-TimeLate Log-Out
PDXI Support 1027 Juhoney br   February 22, 20247:06:03 AM2:20:35 PM7:00:00 AM 2:00:00 PMOn-TimeLate Log-Out
PDXI Support 1036 Ana Marie br   February 19, 20248:26:43 AM 3:40:36 PM8:00:00 AM 2:00:00 PMLate-Log-InLate Log-Out
PDXI Support 1036 Ana Marie br    February 20, 20248:05:51 AM5:32:54 PM8:00:00 AM 2:00:00 PMOn-TimeLate Log-Out
PDXI Support 1036 Ana Marie br    February 21, 20248:07:13 AM3:54:34 PM8:00:00 AM 2:00:00 PMOn-TimeLate Log-Out
PDXI Support 1036 Ana Marie br    February 22, 20248:12:49 AM4:54:34 PM8:00:00 AM 2:00:00 PMLate-Log-InLate Log-Out
Message 1 of 5
294 Views
0
2 ACCEPTED SOLUTIONS
v-huijiey-msft
Community Support
Community Support

‎03-27-2024 07:43 PM

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.

vhuijieymsft_0-1711593640477.png

vhuijieymsft_1-1711593640483.png

 

After the merge is completed, select the expand button in the upper right corner and select the corresponding column.

vhuijieymsft_2-1711593691433.png
vhuijieymsft_3-1711593691435.png

 

The table after data cleaning is as follows.

vhuijieymsft_4-1711593720691.png

 

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:

vhuijieymsft_5-1711593775050.png

 

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!

View solution in original post

Demo.pbix
Message 5 of 5
242 Views
0
lbendlin
Super User
Super User
In response to juhoneyighot

‎03-28-2024 08:25 AM

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

View solution in original post

Message 4 of 5
209 Views
4 REPLIES 4
v-huijiey-msft
Community Support
Community Support

‎03-27-2024 07:43 PM

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.

vhuijieymsft_0-1711593640477.png

vhuijieymsft_1-1711593640483.png

 

After the merge is completed, select the expand button in the upper right corner and select the corresponding column.

vhuijieymsft_2-1711593691433.png
vhuijieymsft_3-1711593691435.png

 

The table after data cleaning is as follows.

vhuijieymsft_4-1711593720691.png

 

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:

vhuijieymsft_5-1711593775050.png

 

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!

Demo.pbix
Message 5 of 5
243 Views
0
lbendlin
Super User
Super User

‎03-27-2024 05:43 PM

I would simplify the ask

 


 

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

lbendlin_0-1711586498144.png
resource work times.pbix
Message 2 of 5
250 Views
0
juhoneyighot
Helper II
Helper II
In response to lbendlin

‎03-28-2024 07:51 AM

@lbendlin 

Applied the insights and it looks like this.

juhoneyighot_1-1711637117770.png

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

juhoneyighot_2-1711637377904.png

 

juhoneyighot_4-1711637417795.png

 

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

Message 3 of 5
217 Views
0
lbendlin
Super User
Super User
In response to juhoneyighot

‎03-28-2024 08:25 AM

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

Message 4 of 5
210 Views

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

View All
Top Solution Authors
View All