Returning a single value from a one-to-many relationship

[purple_SP]

Hello I have two tables. The first is a list of transactions which represent customers changing their product service from one thing to another. The order ID is unique in this table.

Order ID	original product id	new product id	change date
1	123	875	02/11/2022
2	561	123	04/09/2022
3	454	561	10/10/2022

The second table is a list of all products that the customer has and the date that each product service started. In this table the order id is not unique.

Order ID	customer id	product id	start date
1	A	123	02/09/2021
1	A	699	06/06/2021
1	A	747	10/04/2021
2	B	561	01/01/2022
3	C	123	06/07/2020
3	C	454	05/08/2021

I would like to create a new column in the first table which has the date that the original plan was created like so:

Order ID	original product id	new product id	change date	original product start date
1	123	875	02/11/2022	02/09/2021
2	561	123	04/09/2022	01/01/2022
3	454	561	10/10/2022	05/08/2021

I have tried using CALCULATE with RELATEDTABLE and FILTER, kind of like this:

CALCULATE (

    FIRSTDATE( table2[start date]),

    FILTER ( RELATEDTABLE( table2), table2[product id] = table1[original product id] && table2[order id] = table1[order id])

)

But this results in a 'Circular dependency detection error'.

I have also tried the LOOKUPVALUE function like so:

LOOKUPVALUE(

    table2[start date],

    table2[order id],table1[order id],

    table2[product id],table1[original product id]

)

But this results in the multiple values being returned : "A table of multiple values was supplied where a single value was expected."

Any help would be much appreciated.

[purple_SP]

This did the trick. Thanks for your help!

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

Original product start date CC =
MAXX (
    FILTER (
        RELATEDTABLE ( Table2 ),
        Table2[product id] = Table1[original product id]
    ),
    Table2[start date]
)