Forum Discussion
First Post: If Exam is within Assignment Start Time and End Time - return Assignment Name?
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.
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 |
Hi Anonymous
Here is a sample file with the solution https://we.tl/t-UWUhGE0RKIFirst 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 ResultStart 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 ResultEnd 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 ResultAssignment 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 ResultAssignment 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
1 Reply
- tamerj1
Community Champion
Hi Anonymous
Here is a sample file with the solution https://we.tl/t-UWUhGE0RKIFirst 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 ResultStart 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 ResultEnd 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 ResultAssignment 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 ResultAssignment 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