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...
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.