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 time1 on time1.dt = Table1.del_date
join time_dim time2 on time2.dt between Table1.pack_date and Table1.del_date

 

I am trying to achive this in power bi, when i join Table1 with Time1 then all good, but i am finding hard to make the second join to the same table(with different alias) work, any inputs are appreciated.

 

Thanks.

3 Replies

  • selimovd's avatar
    selimovd
    Icon for Most Valuable Professional rankMost Valuable Professional
    5 years ago

    Hey Anonymous ,

     

    how are you doing the join? Do you click it together in Power Query? Or do you load both tables and then try with DAX to get the desired table?

     

    Why don't you just use the query as it is if it works. You can use the SQL statement and then load the data as you want them in your query:

     

    If you need any help please let me know.
    If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
     
    Best regards
    Denis
     
    • 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.