Forum Discussion

Natsu177's avatar
Natsu177
Frequent Visitor
1 year ago
Solved

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

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 ...
Need Help
Tips and Tricks
  • MasonMA's avatar
    MasonMA
    1 year ago

    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]
    )

     

Natsu177's avatar
Natsu177
Frequent Visitor
1 year ago

Thanks MasonMA, lbendlin, and Anonymous — 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!