<!-- https://learnsql.cpetoday.com/analyze/relationships -->
# Relationships and the data model

## 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.

> Diagram: A Product table on the one side filters a Sales table on the many side. Selecting Helmet keeps only the Helmet sale. A filter on Sales does not reach Product.

## 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 `CROSSFILTER` or 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 `USERELATIONSHIP` in 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 in `COUNTROWS(RELATEDTABLE(Sales))` or `SUMX(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).

> **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).
