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'm stuck and would appreciate a hint on how to make the column described below or a link to a thread with a solution.
I have a table with the "journey" of products within the warehouse - from delivery to dispatch in the order. Example data:
type_transfer | id_location | id_product | id_dispatch | date_transfer | dispatch_origin |
dispatch | 2 | 9876 | 4466 | 06-03-2023 05:00:00 | 123 |
drop_product | 2 | 9876 | 4466 | 05-03-2023 08:20:00 | 2 |
get_product | 123 | 9876 | 4466 | 05-03-2023 08:00:00 | 123 |
drop_product | 123 | 9876 | 01-01-2023 11:35:00 | ||
get_product | 1 | 9876 | 01-01-2023 11:30:00 | ||
delivery | 1 | 9876 | 01-01-2023 11:00:00 |
The bold column is the effect I would like to achieve. I would like to insert the value from the "id_location" column from the "type_transfer" = "get_product" rows to the "type_transfer" = "dispatch" rows. For both rows, the value of the "id_dispatch" and "id_product" columns must be the same. In addition, it should be the first value (based on the "data_transfer" column) - sometimes this product route can be longer, and I want its original location.
For the rest of rows value should stay the same as in "locaion_id" column
Solved! Go to Solution.
Try
dispatch_origin =
VAR DispatchOrigin =
MINX(
CALCULATETABLE( TOPN( 1, 'Table', 'Table'[date_transfer], ASC ),
ALLEXCEPT( 'Table', 'Table'[id_dispatch], 'Table'[id_product] ),
'Table'[type_transfer] = "get_product"
),
'Table'[id_location]
)
RETURN IF( 'Table'[type_transfer] = "dispatch", DispatchOrigin, 'Table'[id_location])
Try
dispatch_origin =
VAR DispatchOrigin =
MINX(
CALCULATETABLE( TOPN( 1, 'Table', 'Table'[date_transfer], ASC ),
ALLEXCEPT( 'Table', 'Table'[id_dispatch], 'Table'[id_product] ),
'Table'[type_transfer] = "get_product"
),
'Table'[id_location]
)
RETURN IF( 'Table'[type_transfer] = "dispatch", DispatchOrigin, 'Table'[id_location])
It works perfect! Thank you so much!
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 |
---|---|
110 | |
94 | |
80 | |
67 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |