Fact Tables

Fact tables are the center of a Power BI star schema.

They store measurable business events and connect to dimension tables through relationships.

A fact table answers questions such as:

• How many units were sold?
• What was the total revenue?
• How many products were produced?
• How much inventory changed?

Example:

              DimDate
                 |
                 |
DimCustomer --- FactSales --- DimProduct
                 |
                 |
              DimStore

The fact table contains the business events.

The dimension tables provide the context needed to analyze those events.

What Is a Fact Table?

A fact table contains:

• Numeric measurements
• Transaction records
• Foreign keys connecting dimensions

Example:

FactSales

SalesKey
DateKey
CustomerKey
ProductKey
StoreKey
Quantity
SalesAmount

Each row represents a business event.

Example:

Fact Table Types

Power BI models commonly use several types of fact tables.

Transaction Fact Tables

Transaction facts record individual business events.

Examples:

• Sales transactions
• Customer orders
• Production records
• Inventory movements

Example:

FactSales

TransactionID
DateKey
ProductKey
CustomerKey
Quantity
SalesAmount

Each row represents one transaction.

Snapshot Fact Tables

Snapshot facts capture a point-in-time measurement.

Examples:

• Daily inventory levels
• Monthly account balances
• Employee headcount

Example:

FactInventorySnapshot

DateKey
ProductKey
LocationKey
InventoryQuantity

A snapshot answers:

"How much inventory existed on a specific date?"

Accumulating Snapshot Fact Tables

Accumulating snapshots track a process through multiple stages.

Examples:

• Order processing
• Manufacturing workflow
• Shipping process

Example:

FactOrderProcess

OrderKey
OrderDate
ShipDate
DeliveryDate
CompleteDate

The row is updated as the process moves through stages.

Fact Table Keys

Fact tables usually contain foreign keys.

Example:

FactSales

DateKey
ProductKey
CustomerKey
StoreKey

These connect to dimension tables:

DimDate
    |
    |
FactSales
    |
    |
DimProduct

Keys allow Power BI to filter and analyze facts by different perspectives.

Measures Are Created From Facts

Most DAX measures calculate values from fact table columns.

Example:

Total Sales =
SUM(FactSales[SalesAmount])

Example:

Total Quantity =
SUM(FactSales[Quantity])

The measure changes based on report filters.

Example:

Fact Table Design Principles

Follow these guidelines:

Keep Facts Focused on One Business Process

Good:

FactSales

Contains sales events.

Good:

FactInventory

Contains inventory measurements.

Avoid combining unrelated processes:

Sales + Inventory + Production

Store Keys Instead of Descriptions

Preferred:

ProductKey = 1001

Avoid:

ProductName = "All Terrain Tire"

Descriptions belong in dimension tables.

Avoid Calculated Columns When Possible

Instead of storing:

TotalAmount

create a reusable measure:

Total Sales =
SUM(FactSales[SalesAmount])

Measures provide more flexibility.

Common Fact Table Mistakes

Avoid:

Mixing Different Granularities

Example:

FactSales

One row per transaction

+

Monthly totals

These represent different levels of detail.

Create separate fact tables instead.

Too Many Columns

Avoid adding:

• Customer descriptions
• Product descriptions
• Category names
• Region names

These belong in dimensions.

Fact Table Checklist

A well-designed fact table should:

• Represent one business process
• Have a clear grain
• Contain numeric measurements
• Use dimension keys
• Support reusable DAX measures

Next Steps

Continue learning Power BI data modeling:

• Dimension Tables
• Relationships
• Date Tables

Advanced Topics:

• Slowly Changing Dimensions
• Bridge Tables
• Many-to-Many Relationships