Joining 3 Tables (4 with DimDate)

Easiest path here is in Power Query, not the data model. Two merges on your Main table do it: (1) merge with Mapping using ID = ID1 and expand only the ID2 column, then (2) merge that result with Main again, this time matching on BOTH Date and ID2 as a composite key (Ctrl+click both columns in each table when picking the join keys), and expand the value column as Value2.

That gives you the exact flat table you showed. Modeling it through DimDate with active/inactive relationships also works, but for a denormalized output like this, two PQ merges is much cleaner than fighting bidirectional filters.

If this helped, a thumbs up and accepting the solution would be appreciated.

Best,
Shai Karmani

Step 1) Merge everything in Power Query into one flat table

Start from Main Table, merge Mapping Table on ID = ID1 to bring in ID2, then merge Main Table 2 on both Date and ID2 to bring in Value2

Step 2) Add a DimDate table

Connect DimDate[Date] to FlatTable[Date] with a single active relationship.

CoopLi 

you can do this in PQ

1. merge main and mapping to create a new table

2. then merge new table with main again

pls see the attachment below

As per our understanding your scenario, the issue isn’t really about joining 3 tables—it’s about getting the correct value for ID2 on the same Date context. A simple relationship model won’t fully solve this because you’re effectively doing a self-lookup with an additional Date condition.

Why your current approach fails

You tried:

• Main[ID] → Mapping[ID1]
• Mapping[ID2] → Main2[ID]

This works without Date, but once Date is involved:

• Power BI cannot automatically enforce “same Date + matching ID2”
• Relationships don’t support multi-column joins (ID + Date) in this way

Recommended Approach (Best Practice)

Use a measure-based lookup instead of relying only on relationships.

Step 1: Keep your model simple

Tables:

• Main Table (Date, ID, Value)
• Mapping Table (ID1 → ID2)
• (Optional but recommended) DimDate

Create relationships:

• Main[ID] → Mapping[ID1] (Many-to-one)
• DimDate[Date] → Main[Date]

 No need to physically join Main2

Step 2: Create Value2 using LOOKUP logic

Value2 =
VAR CurrentDate = SELECTEDVALUE(Main[Date])
VAR MappedID =
    RELATED(Mapping[ID2])
RETURN
CALCULATE(
    MAX(Main[Value]),
    FILTER(
        ALL(Main),
        Main[ID] = MappedID &&
        Main[Date] = CurrentDate
    )
)

Result

You’ll get:

Hope this helps.

Thanks!