Calculated column pulling value from other table based on earliest date

[dcor12]

See screenshots of sample data model and tables. The C_VALUE column is the calculated column I am attempting to create with expected results shown.

C_VALUE should populate with the A_VALUE with the the earliest A_STARTDATE. If there are multiple entries with the same start date (See lines A8 and A9), then it would return the lesser of the two values from A_VALUE.

I've been attempting DAX such as:
C_VALUE = LOOKUPVALUE('TABLE A'[A_VALUE], 'TABLE A'[A_STARTDATE], MIN('TABLE A'[A_STARTDATE]))

but it's just giving the same answer for every cell.

Appreciate the help.

TABLE A				TABLE B				TABLE C
A_ID	A_STARTDATE	A_VALUE		B_ID	A_ID	C_ID		C_ID	C_VALUE
A1	1/1/2023	8		B1	A1	C1		C1	8
A2	1/2/2023	5		B2	A2	C1		C2	7
A3	1/3/2024	4		B3	A3	C1		C3	4
A4	1/4/2024	7		B4	A4	C1
A5	1/5/2024	2		B5	A4	C2
A6	1/6/2024	3		B6	A5	C2
A7	1/7/2024	6		B7	A6	C2
A8	1/8/2024	4		B8	A7	C2
A9	1/8/2024	5		B9	A8	C3
A10	1/9/2024	4		B10	A9	C3
				B11	A10	C3

[v-yiruan-msft · Accepted solution]

Hi @dcor12 ,

You can create a calculated column as below in the table 'TABLE C' to get it, please find the details in the attachment.

C_VALUE = 
VAR _aids =
    CALCULATETABLE (
        VALUES ( 'TABLE B'[A_ID] ),
        FILTER ( 'TABLE B', 'TABLE B'[C_ID] = 'TABLE C'[C_ID] )
    )
VAR _mindate =
    CALCULATE (
        MIN ( 'TABLE A'[A_STARTDATE] ),
        FILTER ( 'TABLE A', 'TABLE A'[A_ID] IN _aids )
    )
VAR _minvalue =
    CALCULATE (
        MIN ( 'TABLE A'[A_VALUE] ),
        FILTER (
            'TABLE A',
            'TABLE A'[A_ID]
                IN _aids
                && 'TABLE A'[A_STARTDATE] = _mindate
        )
    )
RETURN
    _minvalue

Best Regards

[dcor12]

Thank you, this works beautifully!