Best Way to Build Visual Using Fields from Two Separate Fact-Dimension-Custom Chains?

[Natsu177]

Hi everyone,

I need help creating a visual in Power BI that combines data from 4 tables.

Fact A → many-to-one → Dim A → one-to-many → Custom table A

Fact A → many-to-one → Dim B → one-to-many → Custom table B

I want to create a visual that includes fields from both Dim A and Dim B, as well as from Custom A and Custom B.

The challenge is that there's no relationship between the two chains — they're completely separate. I’m not sure if I should create a bridge, use DAX (maybe TREATAS?), or what to do.

[Natsu177]

Thanks @MasonMA, @lbendlin, and @v-mdharahman — really appreciate the help.

I followed the bridge table approach and created one using the keys from Fact A. Then I connected the bridge to both Dim A and Dim B, and also to their custom tables (instead of connecting the customs to the dims like before).

All relationships are single-direction, many-to-one. Now I can use fields from both Dim and Custom tables in one visual with no issues. Everything filters cleanly.

Thanks again — your suggestions got me to the right solution!

[v-mdharahman]

Hi @Natsu177,

Thanks for reaching out to the Microsoft fabric community forum.

Given that Dim A -> Custom Table A and Dim B -> Custom Table B are on completely separate relationship paths stemming from Fact A, and there’s no direct relationship between those two paths, a regular visual won’t naturally support combining those fields without some modeling help. As @MasonMA has responded to your query with a measure, kindly go through it and check if it solves your issue otherwise as @lbendlin already mentioned about sample data, you can share it here so that we can work on your issue and try to provide with accurate solution.

I would also take a moment to thank @lbendlin and @MasonMA, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.
Community Support Team

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[MasonMA · Accepted solution]

Hi, 

If I'm in your situation I would create a bridge table to have detailed row-level combinations.

Creating a bridge table is also simpler than other approaches. You just need to use below DAX in a new table function to have all combinations from both tables and build your relationship from there. 

BridgeTable =
SUMMARIZE(
FactA,
DimA[KeyColumn],
DimB[KeyColumn]
)