First Post: If Exam is within Assignment Start Time and End Time - return Assignment Name?

[bdunford]

My first post here.  Sorry if this has been answered already, but I can't seem to find it.  Thank you!

I am trying to find a way to link between two Power BI tables:

Table 1:  Exam Table (fact table) than includes Dates and Times of exams and the Physician who completed the Exam

Table 2: Schedule Assignment Table with the Schedule Assignments of about 100 assigned Physicians who complete Exams. Each Schedule Shift Assignment for each Physician has a Start Date and Time and End Date and Time and a unique identifier (ScheduleEntryID)

I want to write a formula (DAX?) that looks at each Exam in the Exam Table to see if the performed ExamDateTime and Physician is on or between a Physician Schedule Shift Start Datetime and the Schedule Shift End Datetime.  If the exam is within an Assignment, return a calculated column in the Exam table that contains the ScheduleEntryID, otherwise return "NoMatch"

This will help me analyze the productivity of each Shift and Physician.

Example File 

EXAM TABLE:

Physician Name	Exam Final Date & Time	Patient
Anderson, Alan	4/15/2022 23:10	Patient A
Brown, Bob	4/15/2022 12:06	Patient B
Brown, Bob	4/16/2022 0:06	Patient D
Brown, Bob	4/15/2022 11:06	Patient C

SCHEDULE TABLE:

Schedule Entry ID	Start Date	Start Time	End Date	End Time	Assignment Key	Assignment Name	Last Name	First Name
1000	4/15/2022	4:00:00 PM	4/16/2022	2:00:00 AM	a123	Assignment A	Anderson	Alan
1022	4/15/2022	8:00:00 AM	4/15/2022	5:00:00 PM	b234	Assignment B	Brown	Bob

IDEAL OUTPUT:

Physician Name	Exam Final Date & Time	Patient	Schedule Entry ID	Start Date	Start Time	End Date	End Time	Assignment Key	Assignment Name
Anderson, Alan	4/15/2022 23:10	Patient A	1000	3/31/2022	4:00:00 PM	4/1/2022	2:00:00 AM	a123	Assignment A
Brown, Bob	4/15/2022 12:06	Patient B	1022	3/31/2022	8:00:00 AM	3/31/2022	5:00:00 PM	b234	Assignment B
Brown, Bob	4/16/2022 0:06	Patient D	No Match
Brown, Bob	4/15/2022 11:06	Patient C	1022	3/31/2022	8:00:00 AM	3/31/2022	5:00:00 PM	b234	Assignment B

[tamerj1 · Accepted solution]

Hi @bdunford 
Here is a sample file with the solution https://we.tl/t-UWUhGE0RKI

First you need to create a full name calculated column in the schedule table. this will simplify the calculation by relying on a relationship based on Physician Name.

Then you can use the Exam table as a base table in the visual then grapping the columns from the schedule table using mesures

Schedule Entry ID = 
VAR ExanmDateTime = SELECTEDVALUE ( Exam[Exam Final Date & Time] )
VAR MatchingRow = 
    FILTER ( 
        Schedule, 
        VAR CurrentStart = Schedule[Start Date] + Schedule[Start Time] - 1
        VAR CurrentEnd = Schedule[End Date] + Schedule[End Time] - 1
        RETURN
            ExanmDateTime >= CurrentStart && ExanmDateTime <= CurrentEnd
    )
VAR EntryID = MAXX ( MatchingRow, Schedule[Schedule Entry ID] )
VAR Result = COALESCE ( EntryID, "No Match" )
RETURN
    Result

Start Date/Time = 
VAR ExanmDateTime = SELECTEDVALUE ( Exam[Exam Final Date & Time] )
VAR MatchingRow = 
    FILTER ( 
        Schedule, 
        VAR CurrentStart = Schedule[Start Date] + Schedule[Start Time] - 1
        VAR CurrentEnd = Schedule[End Date] + Schedule[End Time] - 1
        RETURN
            ExanmDateTime >= CurrentStart && ExanmDateTime <= CurrentEnd
    )
VAR Result = SUMX ( MatchingRow, Schedule[Start Date] + Schedule[Start Time] - 1 )
RETURN
    Result

End Date/Time = 
VAR ExanmDateTime = SELECTEDVALUE ( Exam[Exam Final Date & Time] )
VAR MatchingRow = 
    FILTER ( 
        Schedule, 
        VAR CurrentStart = Schedule[Start Date] + Schedule[Start Time] - 1
        VAR CurrentEnd = Schedule[End Date] + Schedule[End Time] - 1
        RETURN
            ExanmDateTime >= CurrentStart && ExanmDateTime <= CurrentEnd
    )
VAR Result = SUMX ( MatchingRow, Schedule[End Date] + Schedule[End Time] - 1 )
RETURN
    Result

Assignment Key = 
VAR ExanmDateTime = SELECTEDVALUE ( Exam[Exam Final Date & Time] )
VAR MatchingRow = 
    FILTER ( 
        Schedule, 
        VAR CurrentStart = Schedule[Start Date] + Schedule[Start Time] - 1
        VAR CurrentEnd = Schedule[End Date] + Schedule[End Time] - 1
        RETURN
            ExanmDateTime >= CurrentStart && ExanmDateTime <= CurrentEnd
    )
VAR Result = MAXX ( MatchingRow, Schedule[Assignment Key] )
RETURN
    Result

Assignment Name = 
VAR ExanmDateTime = SELECTEDVALUE ( Exam[Exam Final Date & Time] )
VAR MatchingRow = 
    FILTER ( 
        Schedule, 
        VAR CurrentStart = Schedule[Start Date] + Schedule[Start Time] - 1
        VAR CurrentEnd = Schedule[End Date] + Schedule[End Time] - 1
        RETURN
            ExanmDateTime >= CurrentStart && ExanmDateTime <= CurrentEnd
    )
VAR Result = MAXX ( MatchingRow, Schedule[Assignment Name] )
RETURN
    Result