Power BI Data Modeling: Star Schemas, Relationships, and Date Tables

What You Will Learn

• Understand what a data model is and why it matters
• Design a Star Schema — the gold standard for Power BI models
• Create, edit, and manage relationships between tables
• Understand cardinality and cross-filter direction
• Build calculated columns and use them correctly
• Use date tables — an essential Power BI pattern
• Apply best practices for clean, performant models

---

3.1 What Is a Data Model?

A data model is the structure of tables and the relationships between them inside Power BI. If your queries are ingredients, the data model is the recipe that makes them work together.

Without a proper data model:

• Slicers won't filter across tables
• DAX measures calculate wrong numbers
• Reports are slow and confusing

A good data model makes everything else easier — faster reports, simpler DAX, and clearer insights.

---

3.2 The Star Schema — Your North Star

The Star Schema is the recommended pattern for Power BI data models. It separates data into two types of tables:

Fact Tables

• Contain measurable, transactional data — sales, orders, events, clicks
• Usually have many rows (millions is fine)
• Contain foreign keys that point to dimension tables
• Examples: fact_Sales, fact_Orders, fact_WebEvents

Dimension Tables

• Contain descriptive, lookup data — products, customers, dates, locations
• Usually have fewer rows
• Contain a primary key (unique ID column)
• Examples: dim_Product, dim_Customer, dim_Date, dim_Geography

Star Schema Diagram

         dim_Date
            │
            │ (1)
            │
dim_Customer──(*)─── fact_Sales ──(*)──── dim_Product
                          │
                          │ (*)
                          │
                      dim_Store

• (1) = the "one" side (unique key in dimension)
• (*) = the "many" side (foreign key in fact table)

Why Not Just Use One Big Table?

---

3.3 The Model View

Switch to Model View (diagram icon in the left sidebar) to see and manage your tables visually.

Reading the Diagram

• Each box is a table; each row in the box is a column
• Lines between tables are relationships
• The 1 and ***** symbols show cardinality
• A yellow asterisk ✦ on a column means it is a measure, not a stored column

Hiding Fields from Report View

Right-click any column or table → Hide in report view
Hide foreign keys, internal IDs, and columns users should not interact with directly.

---

3.4 Creating and Managing Relationships

Creating a Relationship

Method 1 — Drag and drop (easiest)
In Model View: drag the primary key column from the dimension table and drop it onto the foreign key column in the fact table.

Method 2 — Manage Relationships dialog
Modeling ribbon → Manage Relationships → New
Select Table 1, its column, Table 2, and its column.

Method 3 — Auto-detect
Modeling → Manage Relationships → Autodetect
Power BI tries to find matches automatically. Review the results carefully.

Cardinality Types

Best practice: Aim for *:1 (many-to-one) relationships everywhere. Many-to-many relationships can cause ambiguous filter propagation.

Cross-Filter Direction

Rule: Use Single direction by default. Use Both only when you specifically need it and understand the consequences.

---

3.5 Relationship Best Practices

Do:

• Every fact table should have a relationship to every dimension it references
• Use integer surrogate keys (not text) as relationship keys — 10x faster
• Keep only active relationships — deactivate unused ones (you can still use them in DAX with USERELATIONSHIP)

Avoid:

• Relating fact tables to other fact tables directly
• Using text columns as join keys for large tables
• Many-to-many relationships without understanding their behavior

Inactive Relationships

Sometimes you need two relationships between the same tables (e.g., fact_Sales has both OrderDate and ShipDate, both pointing to dim_Date). Only one can be active. Use the other in DAX:

Ship Date Sales = 
CALCULATE(
    [Total Sales],
    USERELATIONSHIP(fact_Sales[ShipDate], dim_Date[Date])
)

---

3.6 The Date Table — An Essential Pattern

Always create a dedicated Date table. This enables time intelligence functions (YTD, MTD, same period last year, etc.) to work correctly.

Create a Date Table in DAX

In the Modeling ribbon → New Table:

dim_Date = 
ADDCOLUMNS(
    CALENDAR(DATE(2020,1,1), DATE(2026,12,31)),
    "Year",             YEAR([Date]),
    "Month Number",     MONTH([Date]),
    "Month Name",       FORMAT([Date], "MMMM"),
    "Month Short",      FORMAT([Date], "MMM"),
    "Quarter",          "Q" & QUARTER([Date]),
    "Quarter Number",   QUARTER([Date]),
    "Week Number",      WEEKNUM([Date]),
    "Day of Week",      FORMAT([Date], "dddd"),
    "Day Number",       WEEKDAY([Date]),
    "Year-Month",       FORMAT([Date], "YYYY-MM"),
    "Is Weekend",       IF(WEEKDAY([Date],2) >= 6, TRUE, FALSE)
)

Mark It as a Date Table

Select the dim_Date table → Table Tools → Mark as Date Table → select the Date column

Connect It

Drag dim_Date[Date] onto every date column in your fact tables.

Why it matters: Without a proper Date table, DAX time intelligence like TOTALYTD, SAMEPERIODLASTYEAR, and DATEADD will not work — or worse, will silently return wrong numbers.

---

3.7 Calculated Columns vs. Measures

This distinction is critical and confuses many beginners.

Example — Calculated Column (row-level logic)

// In fact_Sales table — adds a column for each row
Profit = fact_Sales[Revenue] - fact_Sales[Cost]

Example — Measure (aggregation)

// A measure — aggregates across whatever filter context is applied
Total Profit = SUM(fact_Sales[Profit])

Rule of thumb: If you're aggregating (summing, counting, averaging), use a measure. If you're categorizing rows or combining columns, use a calculated column.

---

3.8 Organizing Your Model

Create Measure Tables

Instead of scattering measures across fact tables, create a blank table to hold all measures:

1. Modeling → New Table
2. Formula: _Measures = {0} (creates a single-row table)
3. Hide the default column
4. Move all your measures into this table

This keeps your Fields pane clean and organized.

Display Folders

Group related measures into folders in the Fields pane:

• Select a measure → in the Properties pane enter a folder name in Display folder
• Example: Put Total Sales, Total Cost, Gross Profit into a folder called 📊 Revenue

---

3.9 Hands-On: Build a Star Schema

Starting from the Northwind tables loaded in Module 1:

1. Open Model View
2. Identify which tables are facts (Orders, Order_Details) and which are dimensions (Products, Categories, Customers)
3. Draw a star schema on paper first
4. Create relationships:
   • Order_Details[ProductID] → Products[ProductID]
   • Order_Details[OrderID] → Orders[OrderID]
   • Products[CategoryID] → Categories[CategoryID]
5. Hide all foreign key columns from report view
6. Create a Date table using the DAX formula above
7. Connect Orders[OrderDate] → dim_Date[Date]
8. Mark dim_Date as the date table

---

Module 3 Summary

You now know:

• Star schema separates fact tables (transactions) from dimension tables (descriptions)
• Relationships connect tables and enable cross-filtering
• *:1 (many-to-one) relationships with single-direction filtering are the safest default
• Every model needs a dedicated Date table
• Use calculated columns for row-level logic, measures for aggregations

---

Knowledge Check

1. What is the difference between a fact table and a dimension table? Give an example of each.
2. Why is a dedicated Date table required for time intelligence?
3. When should you use a calculated column vs. a measure?
4. What does "cross-filter direction: Both" do, and when should you avoid it?

---

Next Module

➡️ Module 4 – DAX: Data Analysis Expressions
Master the formula language that powers all calculations in Power BI — from basic SUM to advanced time intelligence.