Data modeling and multiple, unrelated fact tables

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.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s