Data Model Basics¶

The data model is the engine under your report. A poorly designed model makes your DAX measures wrong, your filters unpredictable, and your report slow. A well-designed star schema makes everything easier — measures are simpler to write, filters work correctly, and performance is fast even with millions of rows.

Learning Objectives¶

Explain the difference between a flat table and a proper data model
Design a star schema with fact and dimension tables
Create relationships in Power BI Model view
Identify and resolve many-to-many relationship problems
Understand filter direction and cross-filter behavior

What Is a Data Model?¶

A data model is the set of tables and the relationships between them that Power BI uses to calculate your measures. Without relationships, every table is isolated — a measure on one table cannot filter another.

Compare these two approaches:

Flat table approach (problematic) | order_id | customer_name | customer_region | product_name | product_category | order_date | revenue | |----------|---------------|-----------------|--------------|------------------|------------|---------| | 1001 | Alice | North | Widget A | Electronics | 2024-01-15 | 120.00 | | 1002 | Alice | North | Widget B | Electronics | 2024-01-18 | 85.00 | | 1003 | Bob | South | Gadget C | Home | 2024-01-20 | 200.00 |

Every customer attribute (name, region) is repeated for every order. This is redundant, hard to maintain, and can cause aggregation errors in Power BI.

Star schema approach (correct) Three tables: - fact_orders — one row per order: order_id, customer_id, product_id, order_date, revenue - dim_customers — one row per customer: customer_id, customer_name, region - dim_products — one row per product: product_id, product_name, category, unit_cost

Star Schema¶

The star schema gets its name from its shape: a central fact table with dimension tables radiating outward.

dim_date
    |
    | order_date
    |
dim_customers -- customer_id -- [fact_orders] -- product_id -- dim_products
                                      |
                                  store_id
                                      |
                                dim_stores

Fact Tables¶

A fact table contains measurable events — transactions, orders, page views, sensor readings. Characteristics: - Many rows (can be millions or billions) - Numeric columns you want to sum, count, or average (revenue, quantity, duration) - Foreign keys that link to dimension tables - Usually has a date/time column

Dimension Tables¶

A dimension table contains descriptive attributes of the entities in your fact table. Characteristics: - Fewer rows (customers, products, stores — bounded sets) - Text/category columns used for filtering and grouping - A primary key that the fact table references - Usually has a surrogate key (integer ID, not the natural business key)

Why separate dimensions?

Dimensions allow you to change attributes (a product's category, a customer's region) in one place without touching the fact table. They also make filtering correct — when you filter dim_products by category, Power BI propagates that filter to fact_orders through the relationship.

Creating Relationships¶

In Model view, drag a column from one table to a matching column in another. A line appears representing the relationship.

Relationship Properties¶

Double-click a relationship line to see:

Property	Options	Guidance
Cardinality	One-to-many (:1), Many-to-many (:*), One-to-one (1:1)	One-to-many is the correct type for a star schema
Cross filter direction	Single, Both	Single is correct for star schema — filters flow from dimension to fact
Make this relationship active	Active / Inactive	Only one relationship between two tables can be active at a time

The one rule that covers 90% of model problems

Every relationship in a star schema should be One (dimension table, primary key) to Many (fact table, foreign key), with Single cross-filter direction. If it is not, your data model is probably not a star schema yet.

Many-to-Many Relationships — and How to Fix Them¶

Many-to-many relationships occur when both sides of a join have duplicate values in the join column. They usually indicate a missing intermediate table.

Symptom: Power BI warns "This relationship has many-to-many cardinality" and shows a double asterisk (:) on the relationship line.

Example problem: A fact_orders table has a region column (text), and a dim_targets table also has a region column (text) with monthly targets per region. You try to join them on region. But fact_orders has thousands of rows per region, and dim_targets also has multiple rows per region (one per month).

Fix option 1 — Bridge table Create a dim_regions table with one row per region. Relate both fact_orders and dim_targets to dim_regions.

Fix option 2 — Composite key If the relationship needs to be on multiple columns (e.g., region + month), create a composite key column in both tables and join on that.

Accepting many-to-many with "Both" cross-filter

Power BI lets you accept many-to-many with Both-direction cross-filtering. This creates a relationship, but the results are mathematically unpredictable — filters bleed in both directions and measures return ambiguous totals. This is almost never what you want. Fix the data model instead.

The Date Table¶

Date intelligence DAX functions (TOTALYTD, SAMEPERIODLASTYEAR, etc.) require a properly structured date dimension table. Power BI can auto-create one, but for production work, build your own.

A minimal date table:

Date Table columns:
- Date (date) — the primary key, one row per calendar day
- Year (integer) — e.g., 2024
- Quarter (integer) — 1, 2, 3, or 4
- Month Number (integer) — 1 through 12
- Month Name (text) — "January", "February"
- Week Number (integer) — ISO week number
- Day of Week (integer) — 0 = Sunday, 6 = Saturday
- Is Weekday (boolean)
- Fiscal Year, Fiscal Quarter — if your company uses a non-calendar fiscal year

Marking a date table

In Model view or Data view: right-click the date table → Mark as date table → select the Date column. This tells Power BI's time intelligence engine to use this table as the authoritative calendar. Without marking, TOTALYTD and related functions may produce wrong results.

For senior analysts

The date table should span at least one year before your earliest transaction date and one year after your latest — otherwise rolling 12-month calculations break at the edges. Use CALENDARAUTO() in DAX or generate it in Power Query using a date list.

Filter Direction Deep Dive¶

Single-direction filters flow from the "one" side (dimension) to the "many" side (fact). This is correct for star schema.

Example: A filter on dim_products[Category] = "Electronics" automatically filters fact_orders to only electronics orders. Measures like SUM(fact_orders[Revenue]) now return electronics revenue only.

Both-direction cross-filtering lets filters flow in both directions — which means a selection in the fact table can filter the dimension. This is almost never useful in a star schema and can cause: - Circular filter dependencies - Measures that return incorrect totals - Slicer items disappearing unexpectedly (because the fact table's filter reduces the dimension's visible rows)

For mid-level analysts

Use Both-direction cross-filtering only in bridging scenarios (e.g., a many-to-many bridge table that needs to propagate filters in both directions). In all star schema relationships, use Single.

Previous: 02-connecting-data | Next: [[04-basic-dax]]