Data Modelling & Relationships

Why Data Modelling Matters

Data modelling is the single most important skill in Power BI. Before you write a single DAX formula or build a single visual, the quality of your data model determines whether your reports will be fast, accurate, and maintainable — or slow, incorrect, and fragile.

Think of it this way: garbage in, garbage out. If your data model is poorly structured, every calculation you write becomes harder, every report loads slower, and every answer you get is potentially wrong.

What a Good Data Model Gives You

Benefit	Description
Performance	Queries run fast because the engine can navigate relationships efficiently
Accuracy	Calculations return correct results because filters propagate predictably
Simplicity	DAX formulas are shorter and easier to write
Maintainability	Changes to one part of the model don't break everything else
Scalability	The model handles growing data volumes without redesign

What a Bad Data Model Causes

Slow report load times (sometimes minutes for a single page)
Incorrect totals and subtotals that silently mislead decision-makers
Complex DAX workarounds that nobody can maintain
Circular dependency errors that block development
Ambiguous relationships that produce unexpected results

A well-designed data model in Power BI follows a pattern called the star schema. Before we dive into that, let's understand the building blocks: fact tables and dimension tables.

Star Schema

The star schema is the recommended data modelling pattern for Power BI. It organizes your data into two types of tables — fact tables at the center and dimension tables radiating outward — creating a shape that resembles a star.

Star Schema vs Snowflake Schema

Feature	Star Schema	Snowflake Schema
Structure	Fact table surrounded by denormalized dimension tables	Fact table with normalized dimension tables that branch into sub-dimensions
Dimension tables	Wide and flat (all attributes in one table)	Narrow and deep (attributes split across multiple related tables)
Number of tables	Fewer	More
Query performance	Faster (fewer joins)	Slower (more joins required)
Storage	Slightly more (due to redundancy)	Slightly less
Ease of use	Simpler for report authors	More complex
Recommended for Power BI?	Yes	No

Why Star Schema Is Recommended for Power BI

Power BI's internal engine (VertiPaq) is a columnar database. It compresses repeated values within columns extremely well, which means the "redundancy" in a denormalized star schema costs almost nothing in storage. Meanwhile, the simpler structure means:

Fewer relationships to manage
Filters propagate in one direction from dimension to fact (predictable behavior)
DAX formulas are simpler because you don't need to chain through multiple tables
The Power BI visuals and auto-aggregation features work best with star schemas

Visual Representation of a Star Schema

                ┌──────────────┐
                │  DimDate     │
                │──────────────│
                │ DateKey (PK) │
                │ Year         │
                │ Quarter      │
                │ Month        │
                │ MonthName    │
                └──────┬───────┘
                       │
┌──────────────┐       │       ┌──────────────┐
│  DimProduct  │       │       │  DimCustomer │
│──────────────│       │       │──────────────│
│ ProductKey   ├───┐   │   ┌───┤ CustomerKey  │
│ ProductName  │   │   │   │   │ CustomerName │
│ Category     │   │   │   │   │ City         │
│ SubCategory  │   ▼   ▼   ▼   │ Country      │
└──────────────┘ ┌──────────────┐└──────────────┘
                 │  FactSales   │
                 │──────────────│
                 │ DateKey (FK) │
                 │ ProductKey   │
                 │ CustomerKey  │
                 │ StoreKey (FK)│
                 │ Quantity     │
                 │ UnitPrice    │
                 │ TotalAmount  │
                 └──────┬───────┘
                        │
                ┌───────┴──────┐
                │  DimStore    │
                │──────────────│
                │ StoreKey(PK) │
                │ StoreName    │
                │ Region       │
                │ Country      │
                └──────────────┘

In this star schema, the FactSales table sits at the center. Each foreign key in the fact table points to a primary key in a surrounding dimension table. Filters flow from dimension tables into the fact table.

Fact Tables

A fact table stores the measurable, quantitative data about business events. Each row in a fact table represents a single event or transaction at a specific level of detail called the grain.

What Fact Tables Contain

Column Type	Description	Examples
Foreign keys	Link to dimension tables	DateKey, ProductKey, CustomerKey, StoreKey
Measures (numeric values)	Quantitative data you aggregate	Quantity, UnitPrice, TotalAmount, Discount
Degenerate dimensions	Descriptive data that doesn't warrant its own dimension table	OrderNumber, InvoiceNumber

The Grain

The grain defines what a single row in your fact table represents. Getting the grain right is critical:

Order line level: Each row = one product on one order
Daily level: Each row = one product's sales for one day at one store
Monthly level: Each row = one customer's total purchases for one month

Rule of thumb: Choose the lowest grain available. You can always aggregate up (from daily to monthly) but you cannot disaggregate down (from monthly to daily).

Types of Fact Tables

Type	Description	Example	Additive?
Transaction	One row per event as it happens	Each sale, each click, each shipment	Fully additive — can sum across all dimensions
Periodic Snapshot	One row per entity per time period	Daily inventory levels, monthly account balances	Semi-additive — can sum across some dimensions but not time
Accumulating Snapshot	One row per process, updated as milestones occur	Order fulfillment pipeline (ordered, shipped, delivered dates)	Limited additivity

Example: Transaction Fact Table

FactSales Table:
┌─────────┬────────────┬──────────┬─────────┬──────┬───────────┬─────────────┐
│ SalesKey│ DateKey    │ProductKey│CustKey  │ Qty  │ UnitPrice │ TotalAmount │
├─────────┼────────────┼──────────┼─────────┼──────┼───────────┼─────────────┤
│ 1       │ 20260101  │ 101      │ 501     │ 2    │ 29.99     │ 59.98       │
│ 2       │ 20260101  │ 205      │ 502     │ 1    │ 149.99    │ 149.99      │
│ 3       │ 20260102  │ 101      │ 503     │ 5    │ 29.99     │ 149.95      │
│ 4       │ 20260102  │ 310      │ 501     │ 1    │ 499.99    │ 499.99      │
└─────────┴────────────┴──────────┴─────────┴──────┴───────────┴─────────────┘

Example: Periodic Snapshot Fact Table

FactInventory Table:
┌──────────┬────────────┬──────────┬─────────────┬──────────┐
│ SnapKey  │ DateKey    │ProductKey│ WarehouseKey │ QtyOnHand│
├──────────┼────────────┼──────────┼─────────────┼──────────┤
│ 1        │ 20260101  │ 101      │ W01         │ 500      │
│ 2        │ 20260101  │ 205      │ W01         │ 120      │
│ 3        │ 20260102  │ 101      │ W01         │ 495      │
│ 4        │ 20260102  │ 205      │ W01         │ 119      │
└──────────┴────────────┴──────────┴─────────────┴──────────┘

Notice that you cannot SUM the QtyOnHand across dates — that would double-count inventory. This is why snapshot facts are called semi-additive.

Dimension Tables

A dimension table stores descriptive attributes that provide context to the numbers in fact tables. Dimension tables answer the "who, what, where, when, why" questions about your data.

What Dimension Tables Contain

Column Type	Description	Examples
Surrogate key (PK)	Unique identifier, typically an integer	ProductKey, CustomerKey
Natural/business key	The original identifier from the source system	ProductCode, EmployeeID
Descriptive attributes	Text, categories, hierarchies	ProductName, Category, SubCategory, Color
Hierarchy columns	Columns that form drill-down paths	Country → Region → City

Slowly Changing Dimensions (SCD)

When dimension attributes change over time (a customer moves to a new city, a product gets reclassified), you need a strategy for handling the change.

SCD Type	Strategy	How It Works	Use Case
Type 0	Retain original	Never update the attribute	Original credit score, birth date
Type 1	Overwrite	Replace old value with new value; no history kept	Correcting data entry errors
Type 2	Add new row	Create a new row with the new value; old row is marked as expired using start/end dates and a current flag	Track historical changes (customer address over time)
Type 3	Add new column	Add a "Previous" column alongside the current column	When you only need to know the immediate prior value

SCD Type 2 Example

DimCustomer with SCD Type 2:
┌─────────────┬────────────┬─────────────┬───────────┬────────────┬────────────┬─────────┐
│ CustomerKey │ CustomerID │ Name        │ City      │ StartDate  │ EndDate    │ Current │
├─────────────┼────────────┼─────────────┼───────────┼────────────┼────────────┼─────────┤
│ 501         │ C-1001     │ Alice Smith │ Mumbai    │ 2024-01-01 │ 2025-06-30 │ No      │
│ 502         │ C-1001     │ Alice Smith │ Bangalore │ 2025-07-01 │ 9999-12-31 │ Yes     │
│ 503         │ C-1002     │ Bob Kumar   │ Delhi     │ 2024-01-01 │ 9999-12-31 │ Yes     │
└─────────────┴────────────┴─────────────┴───────────┴────────────┴────────────┴─────────┘

Notice that Alice Smith has two rows — one for when she lived in Mumbai and one for after she moved to Bangalore. The surrogate key (CustomerKey) is different for each row, but the natural key (CustomerID) remains the same.

The Date Dimension

Every Power BI data model should include a dedicated date dimension table. This is so important that it gets its own section later in this chapter.

Role-Playing Dimensions

A role-playing dimension is a single dimension table used multiple times in the model, each time playing a different role. The most common example is the Date dimension.

For example, a FactSales table might have:

OrderDateKey → linked to DimDate (for when the order was placed)
ShipDateKey → linked to DimDate (for when the order was shipped)
DeliveryDateKey → linked to DimDate (for when the order was delivered)

In Power BI, you handle role-playing dimensions by:

Creating multiple copies of the dimension table in Power Query (e.g., DimOrderDate, DimShipDate, DimDeliveryDate)
Or using a single DimDate with one active relationship and marking the others as inactive, then using USERELATIONSHIP() in DAX to activate the inactive ones when needed

Creating Relationships

Power BI can detect and create relationships automatically when you load data, but you should always verify and often create relationships manually.

Auto-Detect Relationships

When you load tables, Power BI looks for columns with matching names and compatible data types to create relationships automatically.

When auto-detect works well:

Column names match exactly (e.g., ProductID in both tables)
Data types are compatible
Values actually correspond between the tables

When auto-detect fails or creates wrong relationships:

Column names don't match (e.g., ProductKey vs ProductID)
Multiple columns could match
The matching creates incorrect cardinality

Best practice: Always review auto-detected relationships in the Model View.

The Model View Interface

To manage relationships in Power BI Desktop:

Click the Model icon in the left sidebar (the icon with three connected boxes)
You'll see all your tables displayed as boxes with their columns listed
Lines between tables represent relationships
The line style indicates cardinality: 1 on one end and * on the many end

Creating Relationships Manually

Method 1: Drag and Drop

Open the Model View
Click on a column in one table (e.g., ProductKey in FactSales)
Drag it to the matching column in another table (e.g., ProductKey in DimProduct)
Release — Power BI creates the relationship

Method 2: Manage Relationships Dialog

Go to Home → Manage Relationships
Click New
Select the first table and column
Select the second table and column
Configure cardinality and cross-filter direction
Click OK

Method 3: From Properties Panel

In Model View, right-click a table
Select Properties
Navigate to the Relationships section

Step-by-Step: Building Relationships for a Sales Model

Let's walk through creating a complete star schema for our sample data:

Step 1: Load all tables — FactSales, DimProduct, DimCustomer, DimDate, DimStore

Step 2: Open Model View and delete any auto-detected relationships that are incorrect

Step 3: Create the following relationships:

From (Fact Side)	To (Dimension Side)	Cardinality	Cross-Filter
FactSales[DateKey]	DimDate[DateKey]	Many-to-One	Single
FactSales[ProductKey]	DimProduct[ProductKey]	Many-to-One	Single
FactSales[CustomerKey]	DimCustomer[CustomerKey]	Many-to-One	Single
FactSales[StoreKey]	DimStore[StoreKey]	Many-to-One	Single

Step 4: Verify by checking that filters from any dimension table correctly filter the fact table

Relationship Properties

Every relationship in Power BI has several configurable properties. Understanding these is essential for building correct models.

Cardinality

Cardinality defines how rows in one table relate to rows in the other table.

Cardinality	Symbol	Description	Example
*One-to-Many (1:)**	1 → *	One row in the dimension matches many rows in the fact	One product appears in many sales transactions
*Many-to-One (:1)**	* → 1	Same as above, viewed from the other direction	Many sales transactions reference one product
One-to-One (1:1)	1 → 1	One row in table A matches exactly one row in table B	Employee table and EmployeeDetails table
Many-to-Many (:)	* → *	Multiple rows in A can match multiple rows in B	Students and courses (each student takes many courses, each course has many students)

Cross-Filter Direction

The cross-filter direction controls how filters propagate between tables through the relationship.

Direction	Description	When to Use
Single	Filters flow from the "one" side to the "many" side only	Default and recommended for most relationships
Both (Bi-directional)	Filters flow in both directions	Use sparingly — needed in some many-to-many scenarios

Active vs Inactive Relationships

Active relationship: Power BI uses this relationship by default when evaluating DAX expressions. Only one active relationship can exist between any two tables.
Inactive relationship: Exists in the model but is not used by default. You activate it in specific DAX formulas using USERELATIONSHIP().

Inactive relationships appear as dashed lines in the Model View.

Viewing Relationship Properties

In Model View, double-click a relationship line (or right-click → Properties)
The Edit Relationship dialog shows:
- The two tables and columns involved
- Cardinality dropdown
- Cross-filter direction dropdown
- "Make this relationship active" checkbox
Modify settings as needed and click OK

Cardinality Deep Dive

Let's explore each cardinality type in detail with practical examples.

One-to-Many (1:*) — The Most Common

This is the standard relationship in a star schema. The dimension table (one side) has unique values, and the fact table (many side) has repeated values.

DimProduct (One Side):          FactSales (Many Side):
┌────────────┬─────────────┐    ┌─────────┬────────────┬──────┐
│ ProductKey │ ProductName │    │ SalesID │ ProductKey │ Qty  │
├────────────┼─────────────┤    ├─────────┼────────────┼──────┤
│ 101        │ Widget A    │    │ 1       │ 101        │ 2    │
│ 102        │ Widget B    │    │ 2       │ 101        │ 5    │
│ 103        │ Gadget C    │    │ 3       │ 102        │ 1    │
└────────────┴─────────────┘    │ 4       │ 101        │ 3    │
                                │ 5       │ 103        │ 7    │
                                └─────────┴────────────┴──────┘

Product 101 ("Widget A") appears in three sales rows. The relationship is 1 (DimProduct) to Many (FactSales).

Many-to-Many (:) — Use with Caution

Many-to-many relationships occur when neither table has unique values in the join column.

Common scenario: A sales rep can cover multiple territories, and each territory can have multiple sales reps.

SalesReps:                    Territories:
┌────────┬──────────────┐     ┌──────────────┬──────────┐
│ RepID  │ RepName      │     │ Territory    │ RepID    │
├────────┼──────────────┤     ├──────────────┼──────────┤
│ R1     │ Alice        │     │ North        │ R1       │
│ R2     │ Bob          │     │ North        │ R2       │
│ R3     │ Charlie      │     │ South        │ R2       │
└────────┴──────────────┘     │ South        │ R3       │
                              │ East         │ R1       │
                              └──────────────┴──────────┘

Solutions for many-to-many:

Bridge table: Create an intermediate table that resolves the many-to-many into two one-to-many relationships
TREATAS: Use DAX to create a virtual relationship without physically modifying the model

Bridge Table Approach:

SalesReps (1) ←→ (*) BridgeRepTerritory (*) ←→ (1) Territories

BridgeRepTerritory:
┌────────┬──────────────┐
│ RepID  │ TerritoryKey │
├────────┼──────────────┤
│ R1     │ North        │
│ R1     │ East         │
│ R2     │ North        │
│ R2     │ South        │
│ R3     │ South        │
└────────┴──────────────┘

One-to-One (1:1) — Consider Merging

A one-to-one relationship means each row in table A matches exactly one row in table B.

When you see 1:1 relationships, ask: Should these two tables be merged into one?

Valid reasons to keep 1:1 separate tables:

Security: Different row-level security rules for each table
Performance: One table has many columns rarely used; keeping them separate reduces model size
Source systems: Data comes from different sources and must be refreshed independently

When to merge: If neither of the above applies, merge the tables in Power Query using Merge Queries to simplify your model.

Cardinality Summary Table

Cardinality	Frequency	Filter Direction	Typical Use	Caution Level
One-to-Many	Very common	Single (dimension → fact)	Dimension to fact table	Low
Many-to-One	Very common	Single (dimension → fact)	Same as above, reverse notation	Low
One-to-One	Uncommon	Either direction	Splitting a table for security/performance	Medium — consider merging
Many-to-Many	Rare	Usually both	Complex scenarios like multi-valued assignments	High — consider bridge tables

Cross-Filter Direction

Cross-filter direction is one of the most misunderstood concepts in Power BI modelling. Getting it wrong can cause subtle calculation errors that are hard to debug.

Single Direction (Default and Recommended)

In single-direction filtering, filters flow from the one side (dimension) to the many side (fact).

DimProduct ──(filter flows)──▶ FactSales

When you select "Widget A" in a slicer:
  → DimProduct is filtered to Widget A
  → That filter flows to FactSales (only Widget A sales are shown)
  → FactSales does NOT filter back to DimProduct

This is predictable, performant, and correct for 95% of scenarios.

Bi-Directional Filtering

In bi-directional filtering, filters flow in both directions.

DimProduct ◀──(filter flows both ways)──▶ FactSales

When you select "Widget A" in a slicer:
  → Filter flows to FactSales (only Widget A sales)
  → Filter ALSO flows back from FactSales to DimProduct
    (other dimension tables connected to FactSales are affected)

When Bi-Directional Is Needed

Scenario	Why Bi-Directional?
Many-to-many with bridge tables	Filters must propagate through the bridge to the other dimension
Showing only products that have sales	Without bi-directional, all products appear even if they had no sales
Complex multi-fact models	Connecting two fact tables through a shared dimension

Risks of Bi-Directional Filtering

Risk	Description
Ambiguity	With multiple bi-directional relationships, there may be multiple filter paths between two tables, causing unpredictable results
Performance	Bi-directional filters force the engine to evaluate more complex query plans
Circular dependencies	Can create circular filter paths that Power BI cannot resolve
Incorrect results	Filters may propagate in ways you don't expect, leading to wrong numbers

Best Practice

Start with single-direction for every relationship. Only change to bi-directional when you have a specific need, and test your calculations thoroughly after making the change.

If you need a dimension table to show only values that exist in the fact table, consider using CROSSFILTER() in DAX rather than changing the model relationship to bi-directional:

Products With Sales =
CALCULATE(
    COUNTROWS(DimProduct),
    CROSSFILTER(FactSales[ProductKey], DimProduct[ProductKey], BOTH)
)

Managing Relationships

Edit a Relationship

Open Model View
Double-click the relationship line between two tables
Modify cardinality, cross-filter direction, or active status
Click OK

Delete a Relationship

Open Model View
Right-click the relationship line
Select Delete
Confirm the deletion

Warning: Deleting a relationship will break any DAX formulas or visuals that depend on it. Check for dependencies first.

Deactivate a Relationship

Double-click the relationship line
Uncheck "Make this relationship active"
Click OK

The line changes to dashed to indicate it's inactive.

Using USERELATIONSHIP() for Inactive Relationships

When you have an inactive relationship (common with role-playing dimensions), use USERELATIONSHIP() inside a CALCULATE() to temporarily activate it:

Sales by Ship Date =
CALCULATE(
    SUM(FactSales[TotalAmount]),
    USERELATIONSHIP(FactSales[ShipDateKey], DimDate[DateKey])
)

How it works:

The active relationship (FactSales[OrderDateKey] → DimDate[DateKey]) is used by default
USERELATIONSHIP() tells Power BI to deactivate the active relationship and use the specified inactive one instead for this calculation only
Outside this measure, the original active relationship remains in effect

Practical Example: Multiple Date Relationships

Suppose your FactSales has three date columns: OrderDate, ShipDate, and DeliveryDate.

Step 1: Create one relationship for each date column to DimDate. Mark one as active (typically OrderDate).

Relationship	Active?
FactSales[OrderDateKey] → DimDate[DateKey]	Yes
FactSales[ShipDateKey] → DimDate[DateKey]	No
FactSales[DeliveryDateKey] → DimDate[DateKey]	No

Step 2: Create measures for each perspective:

Sales by Order Date =
SUM(FactSales[TotalAmount])
-- Uses the active relationship automatically

Sales by Ship Date =
CALCULATE(
    SUM(FactSales[TotalAmount]),
    USERELATIONSHIP(FactSales[ShipDateKey], DimDate[DateKey])
)

Sales by Delivery Date =
CALCULATE(
    SUM(FactSales[TotalAmount]),
    USERELATIONSHIP(FactSales[DeliveryDateKey], DimDate[DateKey])
)

The Date Table

A proper date table is essential for time intelligence in Power BI. Without one, functions like TOTALYTD, SAMEPERIODLASTYEAR, and DATESYTD will not work correctly.

Why You Need a Dedicated Date Table

Reason	Explanation
Time intelligence functions	DAX time intelligence functions require a contiguous date table with no gaps
Custom hierarchies	You need Year → Quarter → Month → Day drill-down paths
Fiscal calendars	Most businesses don't follow the calendar year; you need fiscal year/quarter/month columns
Consistent filtering	All date-based filtering should go through one table, not scattered date columns
Performance	A single date table is more efficient than having date logic in every measure

Creating a Date Table with DAX

Method 1: Using CALENDAR

DimDate =
CALENDAR(
    DATE(2020, 1, 1),    -- Start date
    DATE(2026, 12, 31)   -- End date
)

This creates a single-column table with every date between the start and end dates.

Method 2: Using CALENDARAUTO

DimDate =
CALENDARAUTO()

CALENDARAUTO() scans all date columns in your model and creates a table covering the full range, padded to complete calendar years.

Method 3: Comprehensive Date Table

DimDate =
VAR BaseCalendar = CALENDAR(DATE(2020, 1, 1), DATE(2030, 12, 31))
RETURN
ADDCOLUMNS(
    BaseCalendar,
    "Year", YEAR([Date]),
    "Quarter", QUARTER([Date]),
    "QuarterLabel", "Q" & QUARTER([Date]),
    "Month", MONTH([Date]),
    "MonthName", FORMAT([Date], "MMMM"),
    "MonthShort", FORMAT([Date], "MMM"),
    "MonthYear", FORMAT([Date], "MMM YYYY"),
    "Day", DAY([Date]),
    "DayOfWeek", WEEKDAY([Date], 2),
    "DayName", FORMAT([Date], "dddd"),
    "WeekNum", WEEKNUM([Date], 2),
    "IsWeekend", IF(WEEKDAY([Date], 2) >= 6, TRUE(), FALSE()),
    "FiscalYear", IF(MONTH([Date]) >= 4, YEAR([Date]) + 1, YEAR([Date])),
    "FiscalQuarter",
        SWITCH(
            TRUE(),
            MONTH([Date]) >= 4 && MONTH([Date]) <= 6, 1,
            MONTH([Date]) >= 7 && MONTH([Date]) <= 9, 2,
            MONTH([Date]) >= 10 && MONTH([Date]) <= 12, 3,
            4
        ),
    "YearMonth", YEAR([Date]) * 100 + MONTH([Date]),
    "IsCurrentYear", IF(YEAR([Date]) = YEAR(TODAY()), TRUE(), FALSE()),
    "IsCurrentMonth",
        IF(
            YEAR([Date]) = YEAR(TODAY()) && MONTH([Date]) = MONTH(TODAY()),
            TRUE(),
            FALSE()
        )
)

Marking as Date Table

After creating your date table, you must mark it as a Date Table:

Select the date table in the Data pane
Go to Table tools → Mark as date table
Select the column that contains unique, contiguous dates (the Date column)
Click OK

What marking does:

Enables time intelligence functions
Disables Power BI's auto date/time feature for related columns
Validates that the date column has no gaps, no duplicates, and covers complete years

Date Table Column Reference

Column	Description	Example Values	Sort By
Date	The unique date (primary key)	2026-01-15	—
Year	Calendar year	2026	—
Quarter	Quarter number (1-4)	1	—
QuarterLabel	Formatted quarter label	Q1	Quarter
Month	Month number (1-12)	1	—
MonthName	Full month name	January	Month
MonthShort	Abbreviated month name	Jan	Month
MonthYear	Month and year combined	Jan 2026	YearMonth
Day	Day of month (1-31)	15	—
DayOfWeek	Day of week (1=Mon, 7=Sun)	4	—
DayName	Full day name	Thursday	DayOfWeek
WeekNum	ISO week number	3	—
IsWeekend	Boolean weekend flag	TRUE/FALSE	—
FiscalYear	Fiscal year (April start)	2026	—
FiscalQuarter	Fiscal quarter (1-4)	4	—
YearMonth	Sortable year-month integer	202601	—

Important: For columns like MonthName, you must set the Sort By Column property. Go to the Column tools tab and set MonthName to sort by Month (the number). Otherwise, months will sort alphabetically (April, August, December...).

Calculated Tables

Calculated tables are tables you create using DAX expressions. They are computed during data refresh and stored in the model.

Creating a Calculated Table

Go to Modelling → New Table
Enter a DAX expression in the formula bar

Use Cases for Calculated Tables

Use Case	DAX Expression
Date table	`DimDate = CALENDAR(DATE(2020,1,1), DATE(2030,12,31))`
Distinct values	`UniqueProducts = DISTINCT(FactSales[ProductKey])`
Filtered copy	`HighValueSales = FILTER(FactSales, FactSales[TotalAmount] > 1000)`
What-If parameters	Created automatically by the What-If Parameter feature
Disconnected table	`Metric Selector = {"Revenue", "Profit", "Units"}` for dynamic measure switching
Summary table	`MonthlySummary = SUMMARIZE(FactSales, DimDate[Year], DimDate[Month], "Total", SUM(FactSales[TotalAmount]))`

Example: Creating a Disconnected Slicer Table

MetricSelector =
DATATABLE(
    "MetricName", STRING,
    "MetricOrder", INTEGER,
    {
        {"Revenue", 1},
        {"Profit", 2},
        {"Units Sold", 3},
        {"Average Order Value", 4}
    }
)

This table has no relationship to any other table. It's used with a slicer so users can select which metric to display, combined with a SWITCH measure:

Selected Metric =
SWITCH(
    SELECTEDVALUE(MetricSelector[MetricName]),
    "Revenue", [Total Revenue],
    "Profit", [Total Profit],
    "Units Sold", [Total Units],
    "Average Order Value", [Avg Order Value],
    BLANK()
)

Calculated Columns vs Measures

This is one of the most frequently confused concepts in Power BI. Understanding the difference is critical.

Comparison Table

Feature	Calculated Column	Measure
Created in	Data View (Table tab)	Report View or Data View
Stored	Yes — values are stored in the table during refresh	No — computed at query time
Evaluation context	Row context — has access to each row's values	Filter context — responds to filters from slicers, visuals, etc.
Memory usage	Increases model size (stored per row)	Minimal (computed on demand)
Recalculated	During data refresh only	Every time the visual is rendered
Can be used as	Column in filters, slicers, rows/columns of a visual, sort-by, relationships	Values area of a visual only
Syntax	`ColumnName = expression`	`MeasureName = expression`

When to Use a Calculated Column

Use a calculated column when you need a value that:

Will be used as a filter, slicer, or axis (rows/columns) in visuals
Needs to be part of a relationship
Depends on row-level data and should be pre-computed
Categorizes or segments data (e.g., age groups, price tiers)

Example calculated columns:

-- Full name from first and last
Full Name = Employees[FirstName] & " " & Employees[LastName]

-- Age calculation
Age = DATEDIFF(Employees[BirthDate], TODAY(), YEAR)

-- Price tier categorization
Price Tier =
SWITCH(
    TRUE(),
    Products[ListPrice] < 50, "Budget",
    Products[ListPrice] < 200, "Mid-Range",
    Products[ListPrice] < 500, "Premium",
    "Luxury"
)

When to Use a Measure

Use a measure when you need a value that:

Aggregates data (SUM, AVERAGE, COUNT, etc.)
Should respond to filters (slicers, visual context)
Is used in the values area of a visual
Performs calculations across rows rather than within a single row

Example measures:

Total Revenue = SUM(FactSales[TotalAmount])

Average Order Value =
DIVIDE(
    SUM(FactSales[TotalAmount]),
    DISTINCTCOUNT(FactSales[OrderID])
)

Profit Margin =
DIVIDE(
    SUM(FactSales[TotalAmount]) - SUM(FactSales[TotalCost]),
    SUM(FactSales[TotalAmount])
)

A Critical Difference: Evaluation Context

Consider this expression: SUM(FactSales[TotalAmount])

As a calculated column: Evaluates in row context. But SUM needs filter context. Power BI would SUM the entire TotalAmount column for every single row — giving you the grand total repeated in every row. This is almost never what you want.
As a measure: Evaluates in filter context. The SUM respects whatever filters are active (slicers, visual axes, page filters), giving you the correct subtotal for each context.

Rule of thumb: If the expression contains an aggregation function (SUM, AVERAGE, COUNT, etc.), it should almost always be a measure, not a calculated column.

Data Model Best Practices

Follow these best practices to build clean, performant, and maintainable models.

1. Use Star Schema

Organize your data with fact tables at the center and dimension tables surrounding them. Avoid snowflake schemas unless absolutely necessary.

2. Hide Foreign Keys

Foreign key columns in fact tables (like ProductKey, CustomerKey) are meaningless to report users. Hide them:

Right-click the column in the Data pane
Select Hide in Report View

The column remains available in DAX but won't clutter the field list for report authors.

3. Create a Proper Date Table

Every model should have a dedicated date table, created in DAX or Power Query, marked as a Date Table.

4. Avoid Bi-Directional Filtering Unless Necessary

Start with single-direction. Only use bi-directional when you have a documented reason.

5. Use Meaningful Names

Bad Name	Good Name
`Table1`	`FactSales`
`Column3`	`ProductCategory`
`Measure 1`	`Total Revenue`
`CalcCol1`	`Customer Age Group`

6. Organize Measures in Display Folders

Group related measures into folders:

Select a measure
In Properties, set the Display Folder (e.g., "Revenue Metrics", "Customer Metrics")
The field list will show organized folders instead of a flat list

7. Set Correct Data Types

Ensure every column has the correct data type:

Dates should be Date (not Text)
Numbers should be Decimal Number or Whole Number (not Text)
Keys should be Whole Number or Text (depending on source)

8. Remove Unnecessary Columns

Every column in your model consumes memory. Remove columns that:

Are not needed in any visual, filter, or calculation
Contain IDs or codes that have no analytical value
Are duplicated by other columns

Remove them in Power Query (before they enter the model) rather than hiding them.

9. Create a Measures Table

Create a dedicated table to hold your measures:

Go to Modelling → New Table
Enter: _Measures = ROW("Placeholder", 1)
Move all your measures to this table
Hide the Placeholder column

Prefix the table name with _ so it sorts to the top of the field list.

10. Document Your Model

Add descriptions to tables, columns, and measures:

Select a table, column, or measure
In Properties, enter a Description
Report authors can see this description as a tooltip when hovering over the field

Common Modelling Mistakes

Mistake 1: Using Snowflake Instead of Star

Problem: You have FactSales → DimProduct → DimCategory → DimSubCategory creating a chain.

Solution: Flatten the chain. Add Category and SubCategory columns directly to DimProduct.

Mistake 2: Missing Date Table

Problem: You're using dates from your fact table directly in visuals. Time intelligence functions don't work.

Solution: Create a dedicated DimDate table and link it to your fact table. Mark it as a Date Table.

Mistake 3: Circular Dependencies

Problem: Power BI shows "A circular dependency was detected" error.

Cause: Usually happens when:

Bi-directional relationships create a loop
Calculated columns reference each other
The model has multiple paths between two tables

Solution: Remove bi-directional filters, eliminate redundant relationships, or restructure the model.

Mistake 4: Ambiguous Relationships

Problem: There are multiple paths between two tables, and Power BI doesn't know which to use.

Cause: Two or more active relationships connect the same two tables (directly or indirectly).

Solution: Keep only one active relationship between any two tables. Make others inactive and use USERELATIONSHIP().

Mistake 5: Too Many Bi-Directional Filters

Problem: Performance degrades and calculations return unexpected results.

Solution: Audit every bi-directional relationship. Replace with single-direction where possible. Use CROSSFILTER() in DAX for specific measures that need it.

Mistake 6: One Giant Flat Table

Problem: All data is in a single denormalized table with hundreds of columns.

Solution: Split into fact and dimension tables. This reduces memory usage (VertiPaq compresses smaller tables with fewer distinct values per column more efficiently) and makes the model more maintainable.

Mistake 7: Using Calculated Columns for Aggregations

Problem: A calculated column uses SUM() or AVERAGE() — it shows the same grand total in every row.

Solution: Convert to a measure. Aggregation functions belong in measures, not calculated columns.

Practice Exercises

Exercise 1: Identify Fact and Dimension Tables

Given the following tables in a university database, classify each as a fact table or dimension table:

Table	Columns
Enrollments	EnrollmentID, StudentID, CourseID, SemesterID, Grade, Credits
Students	StudentID, Name, Major, AdmissionYear, GPA
Courses	CourseID, CourseName, Department, CreditHours
Semesters	SemesterID, SemesterName, StartDate, EndDate, AcademicYear
Professors	ProfessorID, Name, Department, HireDate

Questions:

Which table is the fact table? Why?
Which tables are dimension tables?
What is the grain of the fact table?
Draw the star schema showing relationships.

Exercise 2: Fix the Data Model

A colleague built the following model. Identify all the problems:

FactSales has a bi-directional relationship with every dimension table
There is no date table — the Date column in FactSales is used directly in visuals
DimProduct links to DimCategory, which links to DimSubCategory (snowflake)
All foreign key columns (ProductKey, DateKey, etc.) are visible in the report
There are two active relationships between FactSales and DimDate (one for OrderDate, one for ShipDate)

Questions:

List each problem and explain why it's an issue.
Describe how to fix each problem.

Exercise 3: Create a Date Table

Write the DAX to create a comprehensive date table that includes:

Date (the primary date column)
Year, Quarter, Month, Day
MonthName (full name)
DayName (full name)
IsWeekend (TRUE/FALSE)
FiscalYear (starting April 1)
FiscalQuarter

Exercise 4: Role-Playing Dimensions

Your FactOrders table has three date columns:

OrderDate
PromisedDeliveryDate
ActualDeliveryDate

Write the DAX measures to:

Calculate total order amount by Order Date (using active relationship)
Calculate total order amount by Promised Delivery Date (using inactive relationship)
Calculate the number of late deliveries (where ActualDeliveryDate > PromisedDeliveryDate)

Exercise 5: Cardinality Analysis

For each scenario, identify the correct cardinality and cross-filter direction:

Scenario	Cardinality	Cross-Filter
DimEmployee → FactPayroll	?	?
DimDate → FactSales	?	?
FactSales ↔ FactReturns (through DimProduct)	?	?
Students ↔ Courses (students can take many courses, courses have many students)	?	?

Summary

Data modelling is the foundation of every successful Power BI project. In this chapter, you learned:

Star schema is the recommended pattern for Power BI: fact tables at the center, dimension tables around them
Fact tables contain measurable, quantitative data (the "how much" and "how many")
Dimension tables contain descriptive attributes (the "who, what, where, when")
Relationships connect tables; the most common is one-to-many from dimension to fact
Cardinality defines how rows match between tables: one-to-one, one-to-many, or many-to-many
Cross-filter direction controls how filters flow; single direction is the default and recommended
Active vs inactive relationships: only one active relationship between any two tables; use USERELATIONSHIP() for inactive ones
Date tables are essential: create one with DAX, mark it as a Date Table, and link it to your fact tables
Calculated columns are stored per row and evaluated in row context; measures are computed at query time in filter context
Best practices include: use star schema, hide foreign keys, avoid unnecessary bi-directional filters, name things clearly, and organize measures in display folders

In the next chapter, you'll learn DAX Fundamentals — the formula language that brings your data model to life with calculations, aggregations, and business logic.