Join Datetime field on table with Datetime range (Start and End Datetime)

[astallings]

I've got an MSSQL table [incidents] with a datetime field, and a mysql table [schedule] which contains all employee shifts worked with start (datetime) and end (datetime) fields.

I'm trying to build a dashboard to list incomplete incident reports. When an incident is clicked on I'd like to list everyone who was working during the incident.

Can this be done within Power BI?

[v-chuncz-msft · Accepted solution]

@astallings,

You may drag measure below to Table2. It takes advantage of Show Categories With No Data.

Measure =
VAR dt =
    SELECTEDVALUE ( Table1[dt] )
VAR st =
    SELECTEDVALUE ( Table2[st] )
VAR et =
    SELECTEDVALUE ( Table2[et] )
RETURN
    IF ( st <= dt && dt <= et, 1 )

[astallings]

Thanks! That worked great.

a little confused about this part of the code. Wouldn't those 2 variables always be NULL?

VAR st = SELECTEDVALUE ( Table2[st] )
VAR et = SELECTEDVALUE ( Table2[et] )