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

     

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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