Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Join Tables based on between clause

Hello, I have this scenario, sql query below: select branch, sum(case when time2.flag = '1' then 1 else 0 end) as summ, (count(distinct(Table1.abc))) as counts from DimTable Table1 join time_dim...
Anonymous's avatar
Anonymous
Not applicable
5 years ago

Thank you Denis for your response.
sql statement is not an option for me for different reasons.
Need to do this either using DAX or through merging/other options.

 

For now, the Table1 is joined to time1 on time1.dt = Table1.del_date in data model relationships.

But i am stuck in having that second join Time2 as it is uses between clause.

 

I have the flexibility to have multiple time dim's to data model, or just get one but replicate the second via "New Table" option.

daxer-almighty's avatar
daxer-almighty
Icon for Solution Sage rankSolution Sage
5 years ago

It's simple: relationships in PBI are only based on equijoins. If you want something different, you have to either 1) use virtual relationships, i.e., created inside DAX measures using, for instance, TREATAS, or 2) you have to massage the raw data into a correct PBI data format using Power Query (or any external tool that will do the massaging).

 

In our case you could easily create a bridge table that would equi-join both tables but the net effect would be a non-equijoin.