Power BI Data Modelling, Relationships & Joins

Kunu Wako — Sun, 13 Sep 2026 11:30:35 +0000

Introduction

Data modelling involves organizing tables, defining relationships between them, determining how filters move through the model, and deciding how business data should be represented for analysis. In a typical business intelligence environment, data may come from databases, Excel files, APIs, enterprise systems, or cloud platforms. These sources often contain related information in separate tables. For example, a retail company may maintain customers in one table, products in another, transactions in a sales table, and calendar information in a date table. Power BI must understand how these tables are related before it can correctly answer questions such as:

Which products generated the most revenue?
How much did each customer spend?
Which region recorded the highest sales?
How did monthly revenue change over time?

This article explores the major concepts involved in building such a model: flat tables, star schemas, snowflake schemas, fact and dimension tables, relationships, cardinality, filter direction, Power Query joins, and the difference between joins and model relationships.

1. Data Modelling in Power BI

As defined above, data modelling in Power BI is the process of structuring data into logically related tables so that it can be efficiently analyzed. A good model makes it easier for Power BI to determine how information from different tables should interact. Microsoft's Power BI guidance recommends models in which dimension tables are generally used for filtering and grouping, while fact tables are used for summarization. Microsoft also recommends maintaining fact tables at a consistent grain.

Why Data Modelling Matters

Reporting

Report developers can easily understand which fields should be placed in slicers, axes, tables, and measures. A clear model makes calculations easier to write.

For example:

Total Sales =
SUM(FactSales[SalesAmount])

With a properly related DimProduct table, the same measure automatically works when the report is filtered by product, brand, or category.

Performance

Poorly designed models can contain unnecessary columns, repeated descriptive information, excessive relationships, or ambiguous filter paths. A simplified star-shaped model normally allows Power BI to evaluate queries more predictably.

Scalability

A model containing a million sales transactions should not need the customer's name, location, category, and other descriptive values repeatedly stored in every sales row. Dimensions allow descriptive data to be stored separately and reused.

Maintainability

Logical separation makes troubleshooting easier. If product descriptions change, developers know to investigate the product dimension instead of searching a massive sales table.

2. Flat Table Model

A flat table stores most or all information in one table.

For example:

OrderID	Date	Customer	County	Product	Category	Quantity	UnitPrice	SalesAmount
1001	2026-09-01	Amina	Nairobi	Laptop	Electronics	1	85000	85000
1002	2026-09-01	Brian	Nakuru	Mouse	Accessories	2	1500	3000
1003	2026-09-02	Amina	Nairobi	Keyboard	Accessories	1	4500	4500

The table combines transaction information with customer, location, product, and date attributes. There are no relationships because all the required information exists inside the same table.

Advantages

A flat table is simple to understand and can be appropriate for small datasets or quick exploratory analyses. It also avoids the need to create relationships because visualizations can reference columns directly from one table.

Disadvantages

The biggest problem is data repetition. If a customer makes 10,000 purchases, their name, county, and other customer details may appear 10,000 times. This creates:

unnecessary redundancy
larger models
more difficult maintenance
less intuitive organization
potential data quality problems
reduced scalability

A flat table can also make it difficult to distinguish between descriptive attributes and measurable business events.
A flat table can be useful when:

the dataset is small
there is only one analytical subject
a report is temporary
the source is already highly simplified
the model contains few columns
complex analytical relationships are unnecessary

3. Star Schema

A star schema consists of a central fact table surrounded by dimension tables. The fact table contains measurable business events while dimensions contain information describing those events. The structure resembles a star, which gives the schema its name.

Consider a retail sales model:

flowchart TB
    CUSTOMER["DimCustomer<br/>CustomerID<br/>CustomerName<br/>Segment"]
    PRODUCT["DimProduct<br/>ProductID<br/>ProductName<br/>Category"]
    DATE["DimDate<br/>DateKey<br/>Date<br/>Month<br/>Year"]
    LOCATION["DimLocation<br/>LocationID<br/>County<br/>Region"]

    SALES["FactSales<br/>SaleID<br/>CustomerID<br/>ProductID<br/>DateKey<br/>LocationID<br/>Quantity<br/>SalesAmount"]

    CUSTOMER --> SALES
    PRODUCT --> SALES
    DATE --> SALES
    LOCATION --> SALES

Advantages

A star schema provides:

clear separation of facts and dimensions
simple relationships
simpler DAX
predictable filter propagation
improved model readability
easier reporting
good scalability
reduced duplication of descriptive attributes

Disadvantages

Creating a star schema normally requires some preparation. Source systems frequently do not provide perfectly structured fact and dimension tables, so developers may need to:

clean data
create surrogate keys
remove duplicates
build dimension tables
combine multiple operational sources

A star schema can therefore require more initial modelling work than simply loading one flat spreadsheet.

Appropriate Situations

Star schemas are particularly suitable for:

sales analysis
financial reporting
inventory reporting
marketing analytics
customer analytics
operational dashboards
enterprise BI systems

4. Snowflake Schema

A snowflake schema extends a star schema by further normalizing dimensions into related tables. For example, instead of keeping category information inside DimProduct, product categories may have their own table.

flowchart LR
    CATEGORY["DimCategory<br/>CategoryID<br/>CategoryName"]
    PRODUCT["DimProduct<br/>ProductID<br/>ProductName<br/>CategoryID"]
    SALES["FactSales<br/>SaleID<br/>ProductID<br/>CustomerID<br/>DateKey<br/>SalesAmount"]
    CUSTOMER["DimCustomer"]
    DATE["DimDate"]

    CATEGORY --> PRODUCT
    PRODUCT --> SALES
    CUSTOMER --> SALES
    DATE --> SALES

Advantages

Snowflaking can:

reduce duplicated dimension attributes
represent complex organizational hierarchies
closely reflect normalized warehouse structures
allow some dimensions to be reused

Disadvantages

The model contains more tables and relationships. This can create:

more complex filter paths
a less intuitive Fields pane
more relationships to maintain
more work for report developers
potentially more complex queries

Snowflake structures can make sense where dimensions contain substantial reusable hierarchies or where the Power BI model intentionally reflects an existing enterprise data warehouse. However, unnecessary snowflaking should generally be avoided where dimension tables can reasonably be flattened.

5. Comparing the Three Modelling Approaches

Characteristic	Flat Table	Star Schema	Snowflake Schema
Number of tables	Usually one	Several	Several/many
Complexity	Low initially	Moderate	Higher
Data redundancy	High	Low/moderate	Lowest
Relationships	Few/none	Simple	More complex
DAX usability	Acceptable for simple models	Excellent	Can be more complex
Scalability	Limited	High	High
Model readability	Declines as table grows	High	Moderate
BI suitability	Small/simple analysis	Excellent	Specialized scenarios
Maintenance	Difficult at scale	Good	More relationships to manage

For most Power BI business intelligence solutions, the star schema offers the best balance between performance, usability and maintainability.

6. Fact Tables and Dimension Tables

The distinction between fact and dimension tables is fundamental to dimensional modelling.

Fact Tables

A fact table records business events.

Examples include:

a sale
an order
a payment
a website visit
a bank transaction
an inventory movement

Fact tables are normally the largest tables in analytical systems.

7. Dimension Tables

Dimension tables contain descriptive information used to classify, filter, and group business events. Microsoft describes dimension tables as tables that support filtering and grouping, whereas fact tables support summarization.

For example:

Typical dimensions include:

DimCustomer
DimProduct
DimDate
DimLocation
DimEmployee
DimSupplier

8. Measures Versus Descriptive Attributes

An easy way to understand the distinction is:

DIMENSIONS describe:
Who?
What?
Where?
When?

FACTS measure:
How many?
How much?
How long?
How often?

For example:

CustomerName ─┐
ProductName  ─┼──► describe the sale
County       ─┤
Month        ─┘

Quantity     ─┐
Revenue      ─┼──► measure the sale
Profit       ─┘

A report might therefore ask:

What was Total Sales by Product Category in Nairobi during August 2026?

In this question:

Total Sales = fact/measure
Product Category = dimension
Nairobi = dimension
August 2026 = dimension

9. Grain or Granularity

The grain of a fact table defines what one row represents. This must be decided before designing the table. For example:

One row in FactSales represents one product line on one customer transaction.

Suppose order ORD1001 contains three products.

The fact table would contain three rows:

OrderID	ProductID	Quantity
ORD1001	P01	2
ORD1001	P03	1
ORD1001	P09	4

The grain is therefore not one row per order but one row per product per order.

Mixing different grains in the same fact table can produce incorrect aggregations. Microsoft specifically recommends that fact tables load data at a consistent grain.

10. Practical Star Schema Example

Consider an electronics retailer.

The company wants to analyze:

sales by month;
sales by customer;
revenue by county;
sales by product category;
quantity sold by product.

A suitable model is:

flowchart TB
    C["DimCustomer<br/>CustomerID (PK)<br/>CustomerName<br/>Segment"]
    P["DimProduct<br/>ProductID (PK)<br/>ProductName<br/>Category"]
    D["DimDate<br/>DateKey (PK)<br/>Date<br/>Month<br/>Quarter<br/>Year"]
    L["DimLocation<br/>LocationID (PK)<br/>County<br/>Region"]

    F["FactSales<br/>SaleID<br/>CustomerID (FK)<br/>ProductID (FK)<br/>DateKey (FK)<br/>LocationID (FK)<br/>Quantity<br/>SalesAmount"]

    C -->|1 : many| F
    P -->|1 : many| F
    D -->|1 : many| F
    L -->|1 : many| F

Each dimension contains unique keys while those keys can occur repeatedly in the fact table.

11. Relationships in Power BI

A relationship defines how two tables are logically connected. Suppose customer information is stored in DimCustomer and transactions are stored in FactSales. Without a relationship, Power BI does not automatically know which sales belong to which customer.
The relationship might be:

12. Primary and Foreign Keys

Primary Key

A primary key uniquely identifies each record in a table.

For example:

DimCustomer

CustomerID	CustomerName
C001	Amina
C002	Brian
C003	Carol

CustomerID contains unique values.

It can therefore identify a particular customer.

Foreign Key

A foreign key points to the corresponding key in another table.

FactSales

SaleID	CustomerID	Amount
S01	C001	2500
S02	C001	5000
S03	C002	1200
S04	C001	800

C001 appears multiple times because Amina can make several purchases.

Therefore:

DimCustomer                  FactSales

CustomerID                   CustomerID
-----------                  ----------
C001 ─────────────────────── C001
  │                          C001
  │                          C001
C002 ─────────────────────── C002

This produces a one-to-many relationship.

13. Cardinality

Cardinality describes how rows from one table correspond to rows in another table. Power BI supports relationship types including:

one-to-many;
one-to-one;
many-to-many.

14. One-to-Many Relationship (1:*)

This is the most common relationship in a Power BI star schema.

One row on the dimension side can correspond to many rows in the fact table.

DimCustomer          FactSales

C001 ─────────────── S001
  │                  S002
  └───────────────── S008

C002 ─────────────── S003

The model relationship is:

For the 1 side to work correctly, the dimension key must contain unique values.

When to Use It

It is ideal for relationships such as:

Customer → Sales
Product  → Sales
Date     → Sales
Location → Sales

This should normally be the dominant relationship pattern in a star schema.

15. One-to-One Relationship (1:1)

In a one-to-one relationship, each key occurs only once in each table.

Example:

Employee                         EmployeeSecurity
EmployeeID                       EmployeeID
----------                       ----------
E001 ─────────────────────────── E001
E002 ─────────────────────────── E002
E003 ─────────────────────────── E003

Each employee has exactly one corresponding security profile.

Possible reasons for such a design include:

separating sensitive information
combining data from separate systems
temporarily extending an existing entity

However, a one-to-one relationship can sometimes indicate that the two tables should simply be combined. Microsoft notes that one-to-one relationships always filter in both directions and generally recommends considering alternative modelling designs before using them extensively.

16. Many-to-Many Relationships (:)

A many-to-many relationship occurs when multiple rows from one side can relate to multiple rows from the other. Consider students and courses. A student may register for several courses, and each course may contain several students.

Students                     Courses

Alice ─────────────┐      ┌─ Data Engineering
                   ├──────┤
                   │      └─ Machine Learning
                   │
Brian ─────────────┼──────── Data Engineering
                   │
                   └──────── Power BI

In relational modelling, this is usually resolved with a bridge table:

flowchart LR
    S["DimStudent<br/>StudentID"]
    B["BridgeEnrollment<br/>StudentID<br/>CourseID"]
    C["DimCourse<br/>CourseID"]

    S -->|1 : many| B
    C -->|1 : many| B

The bridge table converts the conceptual many-to-many relationship into two one-to-many relationships. Direct many-to-many relationships are supported by Power BI, but they should be used deliberately because they can make filter behavior and calculations harder to reason about.

17. Referential Integrity

Referential integrity means that foreign-key values correctly reference corresponding records.

Suppose FactSales contains:

CustomerID = C900

but there is no C900 in DimCustomer.

The sales row has no matching customer dimension record. Ideally, every sale has a matching customer ID. Maintaining referential integrity prevents unexplained or unmatched records and produces more reliable analytics.

18. Active and Inactive Relationships

Power BI relationships can be active or inactive. An active relationship is automatically used during filter propagation. It appears as a solid relationship line in Model View.

An inactive relationship is represented by a dashed line.

19. Filter Direction

Relationships do more than connect tables. They determine how filters propagate through the model.

Consider:

DimProduct
    1
    │
    ▼
    *
FactSales

Suppose a user chooses:

Category = "Computers"

in a slicer using DimProduct[Category].

Power BI first identifies products belonging to Computers.

DimProduct

P01 Laptop       Computers ✓
P02 Desktop      Computers ✓
P03 Mouse        Accessories
P04 Keyboard     Accessories

The relationship then filters FactSales:

FactSales

P01  85,000 ✓
P01  72,000 ✓
P02  54,000 ✓
P03   1,500 ✗
P04   4,500 ✗

The sales measure therefore evaluates only the transactions belonging to the selected products.

20. Single-Direction Filtering

Single-direction filtering allows filters to travel in one direction.

Typical star-schema behavior is:

DIMENSION          FACT
   1                *
   │                │
   └──────────────► │

For example:

DimProduct ─────► FactSales
DimCustomer ────► FactSales
DimDate ────────► FactSales

This is usually easy to understand:

A product filters sales.

It does not automatically mean:

Sales should filter the product dimension and continue through the model.

For one-to-many relationships, Power BI can use single-direction filtering from the 1 side toward the * side. Single-direction relationships are generally preferable where they satisfy the reporting requirement because the filter behavior remains predictable.

21. Bidirectional Filtering

A bidirectional relationship permits filters to move both ways.

DimProduct ◄──────► FactSales

This can be useful in certain scenarios such as:

bridge-table designs
specific many-to-many requirements
dimension-to-dimension analysis
scenarios where slicers must display only values having corresponding fact data

However, bidirectional filtering should not be enabled everywhere. Microsoft recommends minimizing its use because it can negatively affect query performance and create confusing model behavior.

A major problem is ambiguous filter paths.

Consider:

          DimProduct
          ↕        ↕
       FactSales  Bridge
          ↕        ↕
          DimCustomer

If several bidirectional paths exist, Power BI may have multiple ways of propagating the same filter. Microsoft notes that bidirectional relationships can negatively affect performance and can create ambiguous propagation paths. Power BI may prevent some ambiguous configurations from being created.

22. Joins in Power Query

A join combines rows from two tables based on matching columns. In Power Query, joins are normally created using:

Home → Merge Queries

Consider two tables.

Customers

CustomerID	CustomerName
C001	Amina
C002	Brian
C003	Carol
C004	David

Orders

OrderID	CustomerID	Amount
O101	C001	5000
O102	C001	2500
O103	C003	7000
O104	C005	3000

Notice:

C001 appears in both tables.
C003 appears in both tables.
C002 and C004 have no orders.
order O104 belongs to C005, which is not in the Customers table.

These tables demonstrate each Power Query join type.

Power Query currently provides Left Outer, Right Outer, Full Outer, Inner, Left Anti and Right Anti join options through Merge operations.

23. Left Outer Join

Microsoft defines the Left Outer join as retaining every row from the left table and matching information from the right table. A Left Outer Join keeps:

All rows from the left table plus matching rows from the right table.
Conceptually:

LEFT TABLE              RIGHT TABLE
████████████        ┌───────────────┐
████████████────────█████████████████
████████████        └───────────────┘

Keep ALL left + matches from right

With Customers as the left table:

CustomerID	CustomerName	OrderID	Amount
C001	Amina	O101	5000
C001	Amina	O102	2500
C002	Brian	null	null
C003	Carol	O103	7000
C004	David	null	null

Brian and David remain even though they have no orders.

Practical Use

Use a Left Outer Join when the left table represents the records that must be retained.

For example:

Keep every customer and attach their order details where available.

24. Right Outer Join

A Right Outer Join is effectively the reverse. It retains:

Every row from the right table plus matching information from the left table.

Using Orders as the right table:

CustomerID	CustomerName	OrderID	Amount
C001	Amina	O101	5000
C001	Amina	O102	2500
C003	Carol	O103	7000
C005	null	O104	3000

The C005 order remains even though no corresponding customer exists. Use this when all records from the second table must survive the merge.

25. Full Outer Join

A Full Outer Join keeps every record from both tables.

Customers             Orders
┌─────────┐         ┌─────────┐
│█████████│█████████│█████████│
└─────────┘         └─────────┘

Everything from both sides

Result:

CustomerID	CustomerName	OrderID	Amount
C001	Amina	O101	5000
C001	Amina	O102	2500
C002	Brian	null	null
C003	Carol	O103	7000
C004	David	null	null
C005	null	O104	3000

No records are intentionally discarded.

Practical Use

This is useful for reconciliation tasks such as:

Compare two systems and retain all records regardless of whether a match exists.

26. Inner Join

An Inner Join keeps only records that match in both tables.

Customers           Orders
    ┌─────────────┐
────│ MATCH ONLY  │────
    └─────────────┘

Result:

CustomerID	CustomerName	OrderID	Amount
C001	Amina	O101	5000
C001	Amina	O102	2500
C003	Carol	O103	7000

The following records disappear:

C002 Brian
C004 David
O104 / C005

because they do not have matches on both sides.

Practical Use

Use an Inner Join when only matched records are relevant.

For example: Return only customers who have made purchases.

27. Left Anti Join

A Left Anti Join returns rows from the left table that do not have a match in the right table. Power Query describes this join as returning only left-side rows without corresponding right-side matches.

Result:

CustomerID	CustomerName
C002	Brian
C004	David

These are the customers who have no matching orders.

Customers                     Orders

C001 ───────────────────────► MATCH     remove
C002 ──X                                KEEP
C003 ───────────────────────► MATCH     remove
C004 ──X                                KEEP

Practical Use

Left Anti Join is extremely useful for data-quality analysis.

Examples include:

customers without orders
products never sold
employees without payroll records
database records missing from another system

28. Right Anti Join

A Right Anti Join returns rows from the right table that do not have matches in the left table.

With the example data:

OrderID	CustomerID	Amount
O104	C005	3000

This identifies an order whose CustomerID does not exist in the customer master table.

Practical Use

Right Anti Join can be used to discover:

orphan transactions
unmatched IDs
missing master-data records
synchronization problems between systems

29. Visual Summary of Power Query Join Types

LEFT OUTER
Customers ◄──── priority
Keep all Customers + matching Orders


RIGHT OUTER
priority ────► Orders
Keep all Orders + matching Customers


INNER
Customers ∩ Orders
Keep matches only


FULL OUTER
Customers ∪ Orders
Keep everything


LEFT ANTI
Customers - Orders
Customers with NO matching Orders


RIGHT ANTI
Orders - Customers
Orders with NO matching Customers

A compact comparison is:

Join	Left Matches	Left Non-Matches	Right Matches	Right Non-Matches
Left Outer	✓	✓	✓	✗
Right Outer	✓	✗	✓	✓
Full Outer	✓	✓	✓	✓
Inner	✓	✗	✓	✗
Left Anti	✗	✓	✗	✗
Right Anti	✗	✗	✗	✓

30. Power Query Joins vs Power BI Relationships

Joins and relationships are related concepts but perform fundamentally different tasks.

Power Query Merge

A merge happens during the data preparation stage.

Customers
   +
Orders
   │
   ▼
Power Query Merge
   │
   ▼
Combined Query

A merge can physically bring columns from another query into the resulting table.

For example:

Before Merge

FactSales
ProductID
Quantity
Amount

and:

DimProduct
ProductID
ProductName
Category

After merging and expanding the columns:

Sales
ProductID
ProductName
Category
Quantity
Amount

The product attributes have effectively been added to the sales dataset.

31. Relationship

A Power BI relationship does not physically combine the tables.

Instead:

DimProduct                FactSales
ProductID                 ProductID
ProductName     1 ─── *   Quantity
Category                   Amount

The tables remain separate.

The relationship simply tells the semantic model how they are connected and how filters can propagate between them.

MERGE
Two queries → physically combined result

RELATIONSHIP
Two model tables → remain separate but logically connected

32. When Each Operation Happens

The typical sequence is:

1. DATA SOURCE
      │
      ▼
2. POWER QUERY
   - clean
   - transform
   - merge
   - append
      │
      ▼
3. DATA MODEL
   - relationships
   - cardinality
   - filter direction
      │
      ▼
4. DAX
   - measures
   - calculations
      │
      ▼
5. REPORT
   - visuals
   - slicers
   - dashboards

A merge therefore occurs primarily during the transformation stage. A relationship exists within the semantic model stage.

33. When Should a Merge Be Used?

Merging is appropriate when two datasets logically need to become one query. Examples include:

Adding lookup information during transformation

Transaction table
+
Exchange-rate table
=
Transactions with exchange rates

Combining information representing the same logical entity

Employee details
+
Employee extension attributes
=
Employee dimension

Data validation

Anti joins can identify records missing from another system.

Preparing dimensions

Several source tables may need to be merged before producing a clean DimProduct table.

34. When Should a Relationship Be Used?

Relationships are preferable when tables represent different analytical roles.

For example:

DimCustomer
DimProduct
DimDate
DimLocation
FactSales

These tables naturally represent a star schema.

Flattening all of them through repeated Power Query merges would destroy the logical separation between dimensions and facts.

Instead:

flowchart TB
    A["DimCustomer"] --> F["FactSales"]
    B["DimProduct"] --> F
    C["DimDate"] --> F
    D["DimLocation"] --> F

The model remains easy to understand and extend.

35. Problems With Excessive Merging

Suppose this star model:

DimCustomer ─┐
DimProduct ──┤
DimDate ─────┼──► FactSales
DimLocation ─┘

is converted into:

MegaSalesTable
├── Sale fields
├── Customer fields
├── Product fields
├── Date fields
└── Location fields

This can cause dimensions to be repeated across potentially millions of transaction rows. For example, the string 'Nairobi' may be repeated hundreds of thousands of times instead of being represented through a location dimension.

More importantly, the semantic distinction between:

WHO   = Customer
WHAT  = Product
WHEN  = Date
WHERE = Location
EVENT = Sale

becomes less clear.

Keeping facts and dimensions separate therefore provides organizational as well as technical benefits.

36. Practical Comparison

Suppose management asks:

What were laptop sales in Nairobi during September?

Using a Star Schema

Filters originate in dimensions:

DimProduct
Category = Laptop
      │
      ▼
FactSales
      ▲
      │
DimLocation
County = Nairobi
      ▲
      │
DimDate
Month = September

The same measure can remain:

Total Sales =
SUM(FactSales[SalesAmount])

The relationships supply the filter context. This is one of the main strengths of a properly designed Power BI model: simple measures can answer complex analytical questions because the model handles context.

37. Recommended Power BI Model

For a typical business intelligence project, I would recommend a star schema. Consider a company analysing sales, customers, locations, products, and dates. The model could be:

flowchart TB
    DATE["DimDate<br/>DateKey<br/>Day<br/>Month<br/>Quarter<br/>Year"]
    CUSTOMER["DimCustomer<br/>CustomerID<br/>Customer<br/>Segment"]
    PRODUCT["DimProduct<br/>ProductID<br/>Product<br/>Category<br/>Brand"]
    LOCATION["DimLocation<br/>LocationID<br/>County<br/>Region"]

    SALES["FactSales<br/>SaleID<br/>DateKey<br/>CustomerID<br/>ProductID<br/>LocationID<br/>Quantity<br/>Revenue<br/>Cost"]

    DATE -->|1 to many| SALES
    CUSTOMER -->|1 to many| SALES
    PRODUCT -->|1 to many| SALES
    LOCATION -->|1 to many| SALES

The design would normally use:

Dimension     Relationship      Fact
---------     ------------      ----
DimDate       1 ───────── *     FactSales
DimCustomer   1 ───────── *     FactSales
DimProduct    1 ───────── *     FactSales
DimLocation   1 ───────── *     FactSales

Filter direction would generally be Dimension to Fact, rather than bidirectional relationships everywhere.

38. Why the Star Schema Is Recommended

Query and Report Performance

The star design gives the engine a predictable structure in which relatively small dimensions filter a potentially large fact table. It also avoids unnecessary relationship chains.

DAX Simplicity

A measure such as:

Total Revenue =
SUM(FactSales[SalesAmount])

can be reused for:

Revenue by Product
Revenue by Customer
Revenue by County
Revenue by Month
Revenue by Year

without rewriting the measure. Filter context is supplied by the dimensions.

Model Readability

A developer opening Model View can immediately understand the analytical structure, rather than attempting to interpret dozens of arbitrary table connections.

Scalability

Additional dimensions can be introduced logically.

For example:

DimSalesperson
       │
       ▼
FactSales

Likewise, another fact table can potentially reuse conformed dimensions:

              DimDate
              /     \
             ▼       ▼
       FactSales   FactBudget

Reduced Data Redundancy

Customer and product descriptions do not have to be unnecessarily repeated throughout every fact row.

Maintainability

Changes can be isolated to appropriate tables.

Product changes  → DimProduct
Customer changes → DimCustomer
Calendar logic   → DimDate
Transactions     → FactSales

Ease of Reporting

Report creators naturally choose:

Dimensions → slicers, axes and groupings
Facts      → measures and aggregations

Predictable Filter Propagation

Single-direction filters from dimensions to the fact table create a clear path:

DimProduct ─────► FactSales

instead of a network of unnecessary bidirectional relationships. Microsoft's Power BI modelling guidance similarly emphasizes star-schema concepts, consistent fact-table grain, and clear fact/dimension separation.

39. Recommended Design Principles

For most projects, I would therefore use the following approach:

Build a star schema wherever practical.
Store measurable business events in fact tables.
Store descriptive attributes in dimension tables.
Define a clear and consistent grain for every fact table.
Create unique keys on dimension tables.
Reference those keys as foreign keys in fact tables.
Prefer one-to-many relationships between dimensions and facts.
Prefer single-direction filtering from dimensions to facts.
Use bidirectional relationships only when there is a specific analytical reason.
Use many-to-many relationships carefully and introduce bridge tables when appropriate.
Keep relationships active where they represent the default analytical path.
Use inactive relationships or role-playing dimensions for alternative relationships such as Order Date and Ship Date.
Use Power Query merges for data preparation, rather than as a replacement for proper dimensional modelling.
Avoid turning an otherwise logical dimensional model into one flat table without a clear reason.

Conclusion

Flat tables can be effective for small and simple datasets, but their redundancy and lack of structure make them increasingly difficult to manage as data volumes and reporting requirements grow. Snowflake schemas provide additional normalization and can be useful when dimensions contain complex hierarchies, although the additional tables and relationships can make a Power BI model harder to use. For most business intelligence applications, a star schema provides the strongest balance between simplicity, scalability, performance, maintainability, and analytical flexibility.
Power Query joins solve a different problem. They transform and physically combine query data before or during loading, whereas Power BI relationships preserve separate tables and establish logical connections within the semantic model. Understanding when to merge data and when to relate tables is therefore essential. Ultimately, good Power BI development depends on designing the model before designing the dashboard. When the underlying schema, grain, keys, relationships, and filter directions are correct, DAX becomes simpler, reports become easier to build, and the resulting analytics are both more reliable and easier to maintain.

DEV Community: Kunu Wako

Power BI Data Modelling, Relationships & Joins

Introduction

1. Data Modelling in Power BI

Why Data Modelling Matters

Reporting

Performance

Scalability

Maintainability

2. Flat Table Model

Advantages

Disadvantages

3. Star Schema

Advantages

Disadvantages

Appropriate Situations

4. Snowflake Schema

Advantages

Disadvantages

5. Comparing the Three Modelling Approaches

6. Fact Tables and Dimension Tables

Fact Tables

7. Dimension Tables

8. Measures Versus Descriptive Attributes

9. Grain or Granularity

10. Practical Star Schema Example

11. Relationships in Power BI

12. Primary and Foreign Keys

Primary Key

DimCustomer

Foreign Key

FactSales

13. Cardinality

14. One-to-Many Relationship (1:*)

When to Use It

15. One-to-One Relationship (1:1)

16. Many-to-Many Relationships (:)

17. Referential Integrity

18. Active and Inactive Relationships

An inactive relationship is represented by a dashed line.

19. Filter Direction

20. Single-Direction Filtering

21. Bidirectional Filtering

22. Joins in Power Query

Customers

Orders

23. Left Outer Join

Practical Use

24. Right Outer Join

25. Full Outer Join

Practical Use

26. Inner Join

Practical Use

27. Left Anti Join

Practical Use

28. Right Anti Join

Practical Use

29. Visual Summary of Power Query Join Types

30. Power Query Joins vs Power BI Relationships

Power Query Merge

Before Merge

31. Relationship

32. When Each Operation Happens

33. When Should a Merge Be Used?

Adding lookup information during transformation

Combining information representing the same logical entity

Data validation

Preparing dimensions

34. When Should a Relationship Be Used?

35. Problems With Excessive Merging

36. Practical Comparison

Using a Star Schema

37. Recommended Power BI Model

38. Why the Star Schema Is Recommended

Query and Report Performance

DAX Simplicity

Model Readability

Scalability

Reduced Data Redundancy

Maintainability