Forum Discussion

astallings's avatar
astallings
Regular Visitor
8 years ago
Solved

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

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.         ...
  • v-chuncz-msft's avatar
    v-chuncz-msft
    8 years ago

    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 )
v-chuncz-msft's avatar
v-chuncz-msft
Community Support
8 years ago

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's avatar
astallings
Regular Visitor
8 years ago

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] )