Join table with date with another table with two dates in Direct Query mode

Hi Anonymous ,

*EDIT* I just re-read the title and saw 'Direct Query'. I'm pretty sure that option 3 below will break DQ, and fairly sure option 2 will also break it. Sorry ☹️ When it comes to DQ, I think you're going to have to do it via relationships and measures.

I think you have three options:

1) Pass both tables to the data model, then use measures to specify calculations based on this rough structure:

// Structure concept only - will not work!
_measure =
CALCULATE(
  some stuff,
  tbl1[Date] >= tbl2[Startdate],
  tbl1[Date] <= tbl2[Enddate]
)

2) 'Explode' your SCD table (tbl2) into individual date rows using Power Query, by creating and expanding a new custom column like this:

List.Transform(
  {Number.From([Startdate])..Number.From([Enddate])},
  each Date.From(_)
)

3) Create a three-way conditional join in Power Query, something like this:

Table.AddColumn(
  previousStep,
  "newColumnName",
  (OT) => Table.SelectRows(
    bufferedTbl2,
    each OT[Userid] = [UserID] and OT[Date] >= [Startdate] and OT[Date] <= [EndDate]
  ){0}[fieldToMerge]
)

All three are going to cause you performance issues somewhere with such large tables. Option 1) will push performance issues to the enduser at runtime, option 2) will be on your gateway refresh, option 3) will also be on your gateway refresh.

Pete