Conditional joins

[Kenneth_37]

Hi, I'm new to using power bi and usually work with SSMS. Hoping to find out if what I want to do is possible in power bi.  I think it's best if I use an example.

Table1 columns: StartDate, EndDate, Ward, AvailableBeds

Table2 columns: Patient, Ward, Date, LengthOfStay

I'm hoping to join the two tables above, based on the ward. However, table1 will have multiple records for the wards as the AvailableBeds will change on a periodic basis.

For example Ward A might have:

1 record with a StartDate of 01/01/2020, EndDate 31/03/2020;

1 record with StartDate of 01/04/2020, EndDate 30/06/2020

and a final record with StartDate of 01/07/2020, EndDate 31/12/2021;

In SSMS I would join on ward with the condition that Table2 Date, is between Table1 StartDate and EndDate.

What would be the best approach in PowerBI?

Thanks.

[Anonymous · Accepted solution]

Hi @Kenneth_37 ,

Check the following steps in Power Query Editor.

Step1# Inner join merge table1 and table2 with [Ward] and expand tables.

Step2# Create a custom column with below query:

Column = if [Table2.Date] >= [StartDate] and [Table2.Date] <= [EndDate] then 1 else 0

Step3# Filter table with column = 1.

Result would be shown as below:

Then you can delete and rename the rows if you need.

Pbix as attached.

Best Regards,

Jay

[Kenneth_37]

Thank you! I think this might just work. I'm still working through it and will report back.

[Kenneth_37]

I don't quite think I explained what I'm after well enough.

Because the AvailableBeds changes from month to month and I want to display a yearly snapshot, I would require all of the records from table 1 to still be used.

Thanks for your help anyway.

[Anonymous]

Hello @Kenneth_37 

You can join both the tables based on the ward and provide the start and end date slicer.