Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi - I have two tables - one called ShopDeliveries and one called ShopHistory - the tables are linked together on the Shop Number column.
The answer im looking for is the Shop Owner column on the ShopDeliveries table below. To create this column I need to match the shop delivery with the correct shop owner at the time the delivery was made.
Thanks for your help!
ShopDeliveries
Shop Number | CallingID | Arrival Date | Departure Date | Amount Delivered | Shop Owner |
100345 | 345678 | 10/04/2018 | 11/04/2018 | 50 | John |
100346 | 867546 | 13/01/2021 | 16/01/2021 | 150 | John |
100347 | 296857 | 20/07/2020 | 26/07/2020 | 422 | Amy |
100347 | 981256 | 30/11/2020 | 02/12/2020 | 36 | Justin |
ShopHistory
ShopNumber | ShopOwner | Start Date | End Date | Index |
100345 | John | 01/01/2018 | 15/05/2018 | 3 |
100345 | Sara | 15/05/2018 | 11/11/2019 | 2 |
100345 | Lee | 11/11/2019 | 1 | |
100346 | Tim | 19/08/2018 | 15/09/2020 | 3 |
100346 | Amy | 15/09/2020 | 18/12/2020 | 2 |
100346 | John | 18/12/2020 | 1 | |
100347 | Bill | 14/04/2018 | 20/06/2020 | 3 |
100347 | Amy | 20/06/2020 | 31/10/2020 | 2 |
100347 | Justin | 31/10/2020 | 1 |
Hi, @Tom_G
In order to achieve the above result, I created the model like below picture.
The link to pbix file is down below.
DeliveredShop Owner =
CALCULATE (
SELECTEDVALUE ( History[ShopOwner] ),
FILTER (
VALUES ( History[Start Date] ),
History[Start Date] <= SELECTEDVALUE ( Deliveries[Arrival Date] )
),
FILTER (
VALUES ( History[End Date] ),
History[End Date] >= SELECTEDVALUE ( Deliveries[Departure Date] )
|| History[End Date] = BLANK ()
)
)
https://www.dropbox.com/s/48h3n2b8vl46bx6/TomG.pbix?dl=0
Hi, My name is Jihwan Kim.
If this post helps, then please consider accept it as the solution to help other members find it faster.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
@Tom_G , Try a new column like in ShopDeliveries
maxx(filter(ShopHistory, ShopHistory[ShopNumber] = ShopDeliveries[ShopNumber] && ShopDeliveries[Departure Date]>= ShopHistory[Start Date]
&& ShopDeliveries[Departure Date] >=ShopHistory[End Date]) ,ShopHistory[ShopOwner])
Thanks @Jihwan_Kim - the measure produces the right result. For the sake of integration in my model I was looking for a calculated column.
I tried the DAX from @amitchandak for the calculated column but it's not quite giving the expected result (see new column below). Any ideas how to tweak this? Thanks
ShopNumber Arrival Date Departure Date Shop Owner New Column
100345 10/04/2018 11/04/2018 John
100346 13/01/2021 16/01/2021 John Tim
100347 20/07/2020 26/07/2020 Amy Bill
100347 30/11/2020 02/12/2020 Justin Justin
Hi, @Tom_G
Can you try the below for the calculated column ?
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
@Jihwan_Kim , @amitchandak - would you know how to translate Jihwan's calculated column below into the M language, so I could create the column in the Power Query Editor instead?
Thanks a lot
Thanks a lot @Jihwan_Kim for your help - it works on the test file! I also tweaked the query from @amitchandak below, which also seems to work on the test file.
Unfortunately both queries are a strain on memory consumption on my full data set so BI is having issues processing them.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
99 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
111 | |
92 | |
84 | |
66 |