LOOKUPVALUE with multiple conditions in one table

[jkoclejda]

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

[johnt75 · Accepted 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]) 

[jkoclejda]

It works perfect! Thank you so much!