Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 |
Solved! Go to 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
Proud to be a Super User!
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
Proud to be a Super User!
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!
User | Count |
---|---|
107 | |
88 | |
81 | |
76 | |
73 |
User | Count |
---|---|
112 | |
104 | |
96 | |
74 | |
66 |