Join Datasets Based on a common value and a range of numbers

[Jarrod]

Hi,

I am trying to join two datasets based on a matching column and then a number range. In SQL, I would do this with the following query. I am using Power BI Report Server so many not have access to latest features of Power BI.

SELECT
    SALES.Name
    ,SALES.Amount
    ,SALES.Time
    ,SalesLocation.Location
FROM
    SALES
    INNER JOIN SalesLocation ON Sales.Name = SalesLocation.Name 
    AND Sales.SaleTime > SalesLocation.StartTime
    AND Sales.SaleTime <= SalesLocation.EndTime

 Example dataset:

SALES

Name	Amount	Time
John	15	100
John	10	1000
John	11	1500
Jack	6	200
Jack	25	800

SalesLocation

Name	Location	StartTime	EndTime
John	Warehouse	50	1300
John	Shop	1301	2000
Jack	Warehouse	50	1000

Desired Output

Name	Amount	Sale Time In UTC	Location
John	15	100	Warehouse
John	10	1000	Warehouse
John	11	1500	Shop
Jack	6	200	Warehouse
Jack	25	800	Warehouse

[danextian · Accepted solution]

Hi @Jarrod 

You have to merge Sales with SalesLocatio based on Name and then expand the columns needed (locatin, start and end times). Once expanded, create a custom column to equivalent to this:

Sales.SaleTime > SalesLocation.StartTime
    AND Sales.SaleTime <= SalesLocation.EndTime

Please see attached pbix for details

Please see attached pbix

[Jarrod]

Thanks @danextian, this works perfectly. Unfortunately the performance of this is significantly worse when compared to completing the join in SQL Server. Might be best to let SQL handle this rather than Power BI.

Kudos to you though, thankyou!