Left outer join with many to many relationship

Anthony_G1

Frequent Visitor

1 year ago

FYI to future users who are reviewing this forum post, A bridge table in DAX worked!

TableOne - User_ID

TableTwo - User_ID

User_ID --> User_ID is a Many-to-Many relationship

So I created a DAX bridge table:

User_IDBridge =
DISTINCT(
UNION(
SELECTCOLUMNS(TableOne, "User_ID", TableOne[User_ID]),
SELECTCOLUMNS(TableTwo, "User_ID", TableTwo[User_ID]),
)
)

Then I created a one-to-many relationship from this bridge table to both TableOne and TableTwo, with "Cross-filter direction = Both" turned on for both relationships.

Lastly, I was now successfully able to create a join table:

Main_Reporting_Table = NATURALLEFTOUTERJOIN(TableOne, TableTwo)

Forum Discussion

Left outer join with many to many relationship