How to Lookup a value from a filtered table

[jk13]

Hi all,

i have the following data structure tables:

requests

request_id	name
1	james
2	rob
3	sam

activities

activity_id	request_id	event	date
1	1	CREATE	2022-01-10
2	1	UPDATE	2022-01-12
3	2	CREATE	2022-01-12

I want to add a column "create_date" to the requests-table with the date from the activities where the event is CREATE.

There is a one-to-many relationship between requests and activities.

I know this should not be that hard, but somehow i struggle to come op with an easy solution. My idea was using lookupvalue based on the filtered activities-table.

Thanks for your help!

[Samarth_18 · Accepted solution]

Hi @jk13 ,

Create column with below code:-

create date = 
CALCULATE(
    MAX(activities[date]),
        activities[event] = "CREATE"
)

Output:-

Thanks,

Samarth