Forum Discussion
Join table with date with another table with two dates in Direct Query mode
Hello!
I've been wondering is it possible to join two tables in DirectQ mode when the first one have one date and another - date range?
Example:
Calendar: current date (20211110)
TBL1: Employee
Userid - Date
1000 - 20200101
1000 - 20210101
TBL2: Salary
UserID - Size - Startdate - Enddate
1000 - 100 - 20200101 - 20201231
1000 - 150 - 20210101 - 29991231
In SQL it's quite simple to join this tables
select a.userid, b.salary from tbl1 a
join tbl2 b on a.userid=b.userid and a.date between b.startdate and b.enddate
where a.date='20211110'
But PBI won't let me use join using 2 columns, what's the solution for this? In fact, real data has around 4.5 mil lines per month for ~15 columns, so the only option is to use Directy Query. Creating a view that will cross join each lines is very CPU-load unfriendly and will generate hunderds of millions lines of data.
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
3 Replies
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
I think BA_Pete is right. Trying to do this in the query editor isn't likely to work with DirectQuery.
Either create a view/table at the source that joins these how you want or else do it on the DAX side.
