Power BI – Multiple Fact Tables

[Poweraegg]

For this video we will look at multiple fact tables. We will look at modelling the data so that both fact tables can share dimensions to reduce workload. On top of having multiple fact tables, we will look at how to deal with changes in dimensions between fact tables.

Business Case Requirements

• A table combining both orders and invoices in a single list.

• Detail tables for both orders and invoices in one report page.

• RLS where when an invoice owner is selected, he or she can also see the related order (although not the owner of the order), and vice-versa.

• Ability to filter a product, and see all detail items related to only that product both from orders and invoices.

Data Model Requirements

• Two fact tables related to each other with a 1:M relationship (order to invoice)
• A link/bridge table with order and invoice ids
• Dimensions (Employee & Customer) linked to the link table via primary keys
  • Dimension is related to link table via PK and link table translates dimension key to order key and is then linked to either order fact table or invoice fact table
  • If a value for the owner dimension for example is selected, and the owner is found in the invoice table, also orders must be shown related to that invoice even though the owner may be different.
• Product dimension is linked to details fact tables via inactive relationship
  • DAX is used in measures to define context for product filter à Calculate(Sum([Column]), Userelationship([Product Table PK ProductID],[Order/Invoice Product PK ProductID]))
  • Measure for both Order and Invoice Details needs to be defined
  • To be used in visuals showing details for orders and invoices

Solution Review

• Pros
  • Ability to show records from multiple fact tables in one visual combined
  • Ability to filter records from multiple fact tables based on an OR condition
  • Dimensions that change throughout a data flow process between fact tables can be used as a filter
• Cons
  • Link table will grow and can impact performance
  • We need many to many relationships with security filter

Credit: PowerÆgg | Nadim Abou-Khalil