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.
Relationship properties
- Cardinality: one-to-many (the normal case), many-to-one, one-to-one, many-to-many (avoid unless a bridge table is not possible).
- Cross-filter direction: Single (dimension filters fact) or Both. Both can create ambiguity and slow queries; prefer Single and use
CROSSFILTERor a bridge table inside a measure when you need the other way. - Active versus inactive: only one active path between two tables. Extra paths (Order Date and Ship Date both pointing at Calendar) are inactive and used by
USERELATIONSHIPin a measure. - Referential integrity: if the fact has keys with no dimension row the model adds a (Blank) member. Fix the data rather than relying on it.
- Keys: use integer surrogate keys for relationships; hide them from the field list.
RELATED and RELATEDTABLE
RELATED(Product[Brand])in a calculated column (or iterator) on the many side fetches one value from the one side through the active relationship. It needs row context on the many side.RELATEDTABLE(Sales)on the one side returns that row's related fact rows, for use inCOUNTROWS(RELATEDTABLE(Sales))orSUMX(RELATEDTABLE(Sales), ...).- A measure rarely needs RELATED: filters already flow. RELATED is for calculated columns and iterators that do arithmetic across tables, like
SUMX(Sales, Sales[Quantity] * RELATED(Product[UnitCost])).
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).
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.
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.