CODEWITHSUNDEEP
powerbipowerbi-desktop
Andres Mora
Andres Mora

Reputation: 1117

How to get the earliest date from another table in a one to many relationship?

I have these two tables. Both tables have a relationship by id (one to many)

TableA

id
1
2
3

TableB (date field is a date/time datatype)

id     date
1      1/01/2022 09:00:00
1      1/01/2022 11:00:00
2      1/01/2022 14:00:00
2      1/01/2022 18:00:00
2      1/01/2022 21:00:00
3      1/01/2022 09:00:00
3      1/01/2022 09:10:00

I require to add a calculated column in tableA that takes the earliest date of each id from tableB

Upvotes: 0

Views: 2997

Answers (1)

Strictly Funk
Strictly Funk

Reputation: 358

You can use CALCULATE and FILTER DAX formulas to accomplish this

Date Lookup = 
CALCULATE(
    MIN(TableB[date]),
    FILTER(TableB,
    TableA[id]=TableB[id]))

Upvotes: 1

Related Questions