Power BI · Lesson 3 of 7

Data modelling & relationships

Fact and dimension tables, the star schema, relationships, cardinality and a date table.

A good data model makes reports fast and numbers correct. The standard design is the star schema:

A fact table in the middle with events and numbers: Sales (OrderID, Date, ProductID, CustomerID, Qty, Amount).
Dimension tables around it that describe the facts: Products, Customers, Date, Region.

Relationships connect a dimension's unique key to the fact table — usually one-to-many (one product, many sales), with filters flowing from the dimension to the fact.

Always add a dedicated Date table and mark it as a date table. Time-intelligence functions (year-to-date, last year) depend on it.

Sample data & steps

Power BI Desktop (free, Windows)

Model view:

  Products (1) ─────┐
  ProductID, Name, Category
                    │ *
  Date (1) ──────── Sales (fact) ──────── Customers (1)
  Date, Month, Year  * Date, ProductID,  *  CustomerID, Name, City
                       CustomerID, Qty, Amount

Create the date table (Modeling → New table):
Date =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2026, 1, 1 ), DATE ( 2026, 12, 31 ) ),
    "Year", YEAR ( [Date] ),
    "Month", FORMAT ( [Date], "MMM" ),
    "MonthNo", MONTH ( [Date] )
)
Then: Table tools → Mark as date table → Date column

Result

Relationships created:
Products[ProductID]   1 → *  Sales[ProductID]
Customers[CustomerID] 1 → *  Sales[CustomerID]
Date[Date]            1 → *  Sales[Date]

A slicer on Products[Category] = "Laptops" now filters every Sales visual.

Download Power BI Desktop free from Microsoft (Windows). Re-create the small sample tables in Excel, load them, and follow the steps. Data is fictional.

Key points

Use a star schema: one fact table, several dimension tables.
Relationships are usually one-to-many from dimension to fact.
Filters flow from dimensions to facts.
Add and mark a proper Date table.

Quick check

In a star schema, which table holds the numbers you add up, like Amount and Qty?

Dimension table Fact table Date table Lookup table

Practice task

Split a single flat sales sheet into Sales, Products and Customers tables, load them, create the relationships and confirm a Category slicer filters the sales total.

Interview corner

What is a star schema and why use it?

A model with one central fact table linked to dimension tables. It's simple to understand, keeps the model small and lets Power BI filter and aggregate quickly, and DAX behaves predictably on it.

Why do you need a date table?

Time-intelligence functions such as TOTALYTD and SAMEPERIODLASTYEAR need a continuous date table marked as a date table. It also gives consistent Year, Quarter and Month columns for all visuals.