Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 t...
power bi
  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi Anonymous 
    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

tamerj1's avatar
tamerj1
Icon for Community Champion rankCommunity Champion
4 years ago

Hi Anonymous 
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