Downloads

Relationships and the data model

Star schemas, RELATED, role-playing dates, many-to-many and security.

Last updated

View as Markdown For AI agents All guides as text

On this page

You'll be able to

  • Design a star schema with one fact table and related dimension tables.
  • Use RELATED and LOOKUPVALUE, and set relationship properties correctly.
  • Model role-playing dates and many-to-many relationships.
  • Use a disconnected table to drive slicers, and plan security through relationships.

Star schema first

Model with one fact table per business process in the middle (Sales, Orders, Payments) and dimension tables around it (Calendar, Product, Customer, Store). Relationships are one to many from dimension to fact, single direction. A star schema is smaller, faster and makes DAX simple; snowflaked dimensions (Product to Subcategory to Category) work but flatten them with RELATED columns or Power Query merges when you can.

Product (1 side) Key Product 1 Bike 2 Helmet 3 Gloves Sales (many side) Sale Key Amount S1 1 4,200 S2 2 180 S3 1 3,900 S4 3 220 1 * A slicer on Product (1 side) keeps only Helmet's sales. A filter on Sales does not reach Product.
Filters flow from the one side of a relationship to the many side, never the other way.

Relationship properties

LOOKUPVALUE

LOOKUPVALUE(Product[Brand], Product[ProductKey], Sales[ProductKey]) looks a value up without a relationship (and returns blank or an alternate when not found, an error if several rows differ).

Tip

Slower and clumsier than a relationship; use for tables you cannot relate.

Role-playing dimensions

One Calendar, several date columns in the fact (Order, Ship, Due). Keep the Order Date relationship active and create measures with USERELATIONSHIP for the others:

Sales by Ship Date = CALCULATE([Total Sales], USERELATIONSHIP(Sales[ShipDate], 'Calendar'[Date]))

Or load the Calendar twice (Ship Calendar), which makes both usable in one visual at the cost of a second table.

Many-to-many

Prefer a bridge (factless) table with one-to-many relationships to both sides (for example Customer to Account to Customer): filters then flow through it.

Watch out

Use a many-to-many relationship only when the grain is not recoverable, and understand that the numbers can double count in totals.

A measure can use TREATAS to apply one side's values onto the other without any relationship.

Hierarchies and flattening

Create a hierarchy (Category > Subcategory > Product) in the model for drill down. For snowflake dimensions bring the parent attribute down: Product[Category] = RELATED(Category[Category]) (needs the chain of relationships) or merge the tables in Power Query.

Disconnected tables and slicers that drive measures

A table with no relationships (for example a Measure Choice table, a What-if parameter, a Top N number) can feed a measure: SELECTEDVALUE('Top N'[Top N Value], 10) or a SWITCH that picks a measure. Field parameters (Modeling > New parameter > Fields) are the modern way to let users choose which measure or axis a visual shows.

Security and relationships

Row-level security filters on a dimension flow to the fact through relationships, so keep them single direction. A bidirectional relationship can leak filtering across roles; test with View as.

Model design checklist

Unique keys on the one side; no calculated columns that a measure can replace; hide keys and technical columns; one Calendar marked as the date table; display folders and descriptions for measures; consistent formatting; no unnecessary bidirectional or many-to-many relationships; remove columns you do not use (memory and refresh time).

Want to see it run?

Open a finished Power BI project or Excel workbook built on the same data, or ask an AI assistant that has read these guides.