# Data Modelling, Relationships & Joins in Power BI

akello — Mon, 14 Sep 2026 15:51:29 +0000

1. Data Modelling

A data model is the structural blueprint linking your tables so Power BI can filter, aggregate, and calculate correctly. A well-designed model improves DAX simplicity, report performance, scalability, and long-term maintainability.

Schema	Structure	Pros	Cons	Best For
Flat Table	One wide table, all columns together	Simple, no relationships needed	High redundancy, slow on large data, hard to maintain	Small, one-off datasets
Star Schema	One fact table + denormalized dimensions directly connected	Fast performance, simple DAX, easy to navigate	Some data redundancy in dimensions	Most standard BI reporting
Snowflake Schema	Dimensions normalized into sub-dimensions	Less redundancy, smaller storage	More joins/relationships, slower, complex DAX	Very large, highly structured dimension hierarchies

Flat Table:            Star Schema:                 Snowflake Schema:
[ One big table ]      DimCustomer   DimDate         DimCustomer─DimRegion
                            \         /               DimProduct─DimCategory
                          FactSales                        \      /
                            /        \                    FactSales
                     DimProduct   DimLocation                /
                                                        DimDate

2. Fact vs Dimension Tables

Fact tables (e.g., FactSales, FactOrders) store measurable, numeric business events (SalesAmount, Quantity) plus foreign keys — defined at a specific grain (e.g., one row per order line). Dimension tables (DimCustomer, DimProduct, DimDate, DimLocation) store descriptive attributes used to filter and group facts. Example: FactSales connects to DimProduct, DimCustomer, DimDate, and DimLocation — a classic star schema.

3. Relationships

A relationship links tables via keys so filters flow correctly across the model.

One-to-Many (1:*) — e.g., DimCustomer[CustomerID] → FactSales[CustomerID]. Standard for fact-to-dimension links.
One-to-One (1:1) — e.g., Employee ↔ EmployeeDetails. Rare; usually signals tables should be merged.
Many-to-Many (*:*) — e.g., Students ↔ Courses. Use only when no unique key exists; avoid otherwise due to ambiguity risk.

Key concepts: Primary Key (unique row identifier, e.g., CustomerID in DimCustomer), Foreign Key (repeats in FactSales to link back), Cardinality, Referential Integrity (every fact key must exist in its dimension), and Active vs Inactive relationships (only one active path between two tables at a time; inactive paths need USERELATIONSHIP in DAX).

4. Filter Direction

Single-direction: filters flow dimension → fact (default, recommended). Selecting a product in DimProduct filters FactSales.
Bidirectional: filters flow both ways — useful for many-to-many bridges, but risks ambiguous filter paths and unnecessary model complexity. Use sparingly.

5. Joins in Power Query (Merge)

Join Type	Keeps
Left Outer	All of left table + matches from right
Right Outer	All of right table + matches from left
Full Outer	All rows from both tables
Inner	Only matching rows from both
Left Anti	Left rows with no match in right
Right Anti	Right rows with no match in left

Example: Customers ⋈ Orders — Inner join returns only customers who placed orders; Left Anti returns customers who never ordered.

6. Power Query Joins vs Power BI Relationships

A Merge in Power Query happens at the ETL stage and physically combines columns into one new table. A Relationship happens at the model stage and links tables logically without duplicating data. Use merges sparingly (only when you truly need combined columns) — excessive merging flattens the model, increases redundancy, and reduces scalability. Keeping facts and dimensions separate, linked by relationships, preserves a clean, performant star schema.

7. Recommendation

For most BI projects, a Star Schema is preferable: it balances performance, simple DAX, redundancy, and readability far better than a flat table (too redundant/slow at scale) or snowflake (unnecessary join complexity for typical reporting needs). Implement one-to-many relationships from dimensions to the fact table, keep filters single-direction by default, and reserve bidirectional or many-to-many relationships for specific bridging scenarios only.
PowerBI_Data_Modelling_Article.md
Displaying PowerBI_Data_Modelling_Article.md.

DEV Community: akello