Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
RaoSM2021
Regular Visitor

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 3
selimovd
Super User
Super User

Hey @RaoSM2021 ,

 

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:

selimovd_0-1624647856875.png

 

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
 

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.

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors