Model Star Schema and Relationships

What it is

In a semantic model, a star schema is a set of tables joined by relationships. Filters flow along those relationships, from a dimension such as Product to a fact table such as Sales, and that flow is what makes a measure show the right number in every cell.

Most relationships are simple one-to-many links. The exam focuses on the harder cases: dimensions used in several roles, bridge tables and many-to-many relationships.

How it works

Relationship properties

Good star schema habits in the model: hide fact table key columns, mark a dedicated date table with Mark as date table, and turn off Auto date/time, which creates a hidden date table for every date column.

Role-playing dimensions

A Sales table may have OrderDateKey and ShipDateKey, both pointing to the same Date table. Only one relationship can be active; the other is inactive. A measure activates it with USERELATIONSHIP:

Sales by Ship Date =
CALCULATE (
    [Total Sales],
    USERELATIONSHIP ( Sales[ShipDateKey], 'Date'[DateKey] )
)

The alternative is to load the Date table twice, as Order Date and Ship Date. That lets users slice by both at once without special measures, at the cost of a second table.

Bridge tables

A bank customer can hold several accounts, and a joint account has several customers. Neither table can sit on the one side, so a bridge table lists every customer and account pair.

A filter on Customer reaches the bridge, but by default it does not continue from the bridge to Account, because that relationship filters the other way. You have two options:

1. Set the Account to CustomerAccount relationship to Both, so the bridge filters Account.
2. Keep it Single and enable the direction only inside the measures that need it, with CROSSFILTER:

Customer Balance =
CALCULATE (
    SUM ( Transactions[Amount] ),
    CROSSFILTER ( CustomerAccount[AccountID], Account[AccountID], Both )
)

Expect non-additive totals: a joint account is counted under each of its customers, but only once in the grand total.

Many-to-many cardinality

A sales target table stored by Category and month cannot relate one-to-many to the Product table, because Category repeats in both tables. A many-to-many cardinality relationship on the Category column joins them directly. It is a limited relationship, so unmatched keys do not get a blank row.

When the relationship should exist only for one calculation, a virtual relationship with TREATAS does the same job without changing the model:

Target Amount =
CALCULATE (
    SUM ( Targets[Amount] ),
    TREATAS ( VALUES ( 'Product'[Category] ), Targets[Category] )
)

Why Both is used sparingly

Bidirectional filters can create ambiguous paths when two routes connect the same tables, which Power BI refuses or resolves in ways users do not expect. They also make queries slower because more tables are filtered. Use them on bridge relationships, not as a default.

A worked example

An insurer models policies, customers and claims. A policy can have several named customers.

1. Fact table Claims relates many-to-one to Policy.
2. Bridge table PolicyCustomer holds one row per policy and customer pair, related to both Policy and Customer.
3. Analysts filter claims by customer, so the Policy to PolicyCustomer relationship is set to Both; all other relationships stay Single.
4. Claims have ClaimDate and ReportedDate. ClaimDate is active, and a Claims by Reported Date measure uses USERELATIONSHIP for the other.

How the exam asks about this

• "Report sales by order date and by ship date with one Date table." An inactive relationship plus USERELATIONSHIP in a measure.
• "Customers share accounts; filter transactions by customer." A bridge table with the bridge to Account direction set to Both, or CROSSFILTER.
• "Relate targets at category grain to a product-level model." A many-to-many relationship on Category, or TREATAS in the measure.