Chapter 3 of Analyzing data With Microsoft Power BI and Power Pivot for Excel discuss the use of multiple fact tables in data modeling…. Let’ go!
The challenge in module 3
Creating cross-filters between fact tables so that your dimensions, on both sides of your fact tables, are correctly related.
A typical example is sales (customers, products) and purchases (suppliers, products). There is no direct link between suppliers and sales. Or between customers and purchases.
You can use DAX to handle a lot of scenarios or you can change your data model. The rule of thumb is to change your model!
Bridge tables
If you have complex models with multiple fact tables, you can solve the relationship by building a bridge table.
With purchases and sales tables, you can build a product table to bridge your two fact tables. This bridge table should include information that links your purchases and your sales.
Now you will be able to filter on customers, through sales, products (bridge), purchases and all the way to suppliers if that is what you are looking to do.
