Joining 3 Tables (4 with DimDate)

[CoopLi]

Seems like a FAQ, but I haven't been able to apply what I read into my use case.

Main Table, contains timestamped values of all entities: 

Date | ID | Value

4/30 | A | 20

4/30 | B | 21

4/30 | C | 22

4/29 | A | 70

4/29 | B | 65

4/29 | C | 12

Mapping Table, contains relationship between the entities

ID2 | ID2

A | B

B | B

C | A

Main Table 2, a clone ("Reference") of the Main Table, to be joined and used to supply the values for ID2 and Value2

Intended Results:

Date | ID1 | Value1 | ID2 | Value2

4/30 | A | 20 | B | 21 

4/30 | B | 21 | B | 21 

4/30 | C | 22 | A | 20

I started with a non-timestamped version and everything was ok. I linked Main.ID <-> Mapping.ID1 and Mapping.ID2 <-> Main2.ID. But I can't get it work with a date.

Also I read some posts this kind of data would better require a DimDate . Appreciate your advice on how to manage these 3 (or 4) tables. Thanks.

[Shai_Karmani · Accepted solution]

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

[cengizhanarslan · Accepted solution]

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.

[ryan_mayu · Accepted solution]

@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

[v-ssriganesh]

Hello @CoopLi,

We hope you're doing well. Could you please confirm whether your issue has been resolved or if you're still facing challenges? Your update will be valuable to the community and may assist others with similar concerns.

Thank you.

[v-ssriganesh]

Hi @CoopLi,

Thank you for posting your query in the Microsoft Fabric Community Forum, and thanks to @SamInogic , @ryan_mayu, @cengizhanarslan & @Shai_Karmani for sharing valuable insights.

Could you please confirm if your query has been resolved by the provided solutions? This would be helpful for other members who may encounter similar issues.

Thank you for being part of the Microsoft Fabric Community.

[SamInogic · Accepted solution]

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:

Date	ID	Value	ID2	Value2
4/30	A	20	B	21
4/30	B	21	B	21
4/30	C	22	A	20

Hope this helps.

Thanks!

[ryan_mayu · Accepted solution]

@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

[cengizhanarslan · Accepted solution]

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.

[Shai_Karmani · Accepted solution]

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