Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hey Community!
So I have the following problem:
I have 2 tables, one containing different bids for a product, and one containing the price, date etc. to which the product was sold.
The tables look like this:
Table bids:
Bid_id | Start_time | End_time | Product_type | price |
1 | 18.01.2020 06:00:00 | 18.01.2020 06:02:33 | blue | 5 € |
2 | 18.01.2020 06:00:07 | 18.01.2020 06:00:43 | blue | 7 € |
3 | 18.01.2020 06:01:10 | 19.01.2020 15:03:15 | red | 3 € |
4 | 18.01.2020 06:02:20 | 18.01.2020 06:05:44 | blue | 6 € |
Start_time and End_time define the time, on which the bid was placed/taken down.
Table sells:
Sell_id | Sell_time | Product_type | Price |
1 | 18.06.2020 06:00:31 | blue | 6,50 € |
2 | 18:06.2020 06:51:03 | red | 2,50 € |
The sell_id and the bid_id have no relation with each other.
What I want to find out is, what is the maximum bid to the time we sold the product (how much more expensive could we sell). So if we take sell_id 1, it should check, which bids for this specific product were active during the sell_time (in this case bid_id 1 and 2) and give back the higher price (in this case bid_id 2).
So a simple IF(start_time < sell_time AND end_time > sell_time) would suffice, however I am struggling with creating an actual measure, since those 2 tables have no relation with each other.
I hope, this somehow explains my problem. I am grateful for ANY help you can provide. Thanks in advance!
Solved! Go to Solution.
Try this measure:
Max Bid =
VAR vProduct =
MAX ( TableSells[Product_type] )
VAR vSellTime =
MAX ( TableSells[Sell_time] )
VAR vTableBids =
FILTER (
ALL ( TableBids ),
TableBids[Product_type] = vProduct
&& vSellTime >= TableBids[Start_time]
&& vSellTime <= TableBids[End_time]
)
VAR vResult =
MAXX ( vTableBids, TableBids[Price] )
RETURN
vResult
In the sample data, I assumed the Sell_time month should be "01" instead of "06".
Proud to be a Super User!
Try this measure:
Max Bid =
VAR vProduct =
MAX ( TableSells[Product_type] )
VAR vSellTime =
MAX ( TableSells[Sell_time] )
VAR vTableBids =
FILTER (
ALL ( TableBids ),
TableBids[Product_type] = vProduct
&& vSellTime >= TableBids[Start_time]
&& vSellTime <= TableBids[End_time]
)
VAR vResult =
MAXX ( vTableBids, TableBids[Price] )
RETURN
vResult
In the sample data, I assumed the Sell_time month should be "01" instead of "06".
Proud to be a Super User!
Wow thank you for the fast answer!
Thank you very much! This works perfectly and is way faster on big databases than other solutions i have tried!
Have a nice day!
PS: Yes, i meant sell_time month "01" 🙂