Star schema & relationship cardinality

A Power BI model is not a spreadsheet and not a normalised database. The shape that makes both DAX and the VertiPaq engine happy is the star schema: one narrow fact table in the middle, surrounded by short dimension tables.

Fact and dimension tables

For Rajlaxmi Distributors:

Facts hold numbers and keys; dimensions hold the text you slice by. Keep descriptive columns out of the fact table — a Brand column repeated across 8 million invoice lines compresses far worse than a 400-row Product table.

Cardinality and filter direction

Relationships are created by dragging Product[ProductKey] onto Sales[ProductKey]. Power BI then asks two things:

• Cardinality — almost always one-to-many (1:*), with the one side on the dimension.
• Cross-filter direction — Single by default. Filters flow down the "one" side into the fact table, never back up.

That direction is the whole point. Selecting Brand = "Parle" on a slicer filters Product to a few rows, which filters Sales to those rows, and SUM(Sales[NetAmount]) recalculates. Selecting a sale does not filter Product.

The bi-directional trap

Setting cross-filter to Both looks like a quick fix when a slicer "does not work". It introduces two real problems:

1. Ambiguity. With two fact tables (Sales and Targets) both joined to Store and Date, a bi-directional relationship creates more than one path between tables. The engine either refuses the model or silently picks a path, and totals stop matching the source system.
2. Performance. Every filter now propagates in both directions, expanding the work VertiPaq does per visual.

Use Single everywhere, and when you genuinely need upward filtering, do it in one measure with CROSSFILTER or TREATAS rather than changing the model for everyone.

Role-playing dimensions

Sales has both an InvoiceDate and a DeliveryDate. You cannot have two active relationships to Date. Create both, leave the delivery one inactive (dashed line), and activate it inside a measure:

Delivered Value =
CALCULATE (
    SUM ( Sales[NetAmount] ),
    USERELATIONSHIP ( Sales[DeliveryDate], 'Date'[Date] )
)

Key takeaways

• One fact table, many small dimensions, single-direction 1:* relationships.
• Hide key columns (ProductKey, StoreKey) from report view so users slice by Brand, not by a number.
• Avoid many-to-many cardinality unless you truly have a bridge table; it changes blank-handling and totals.
• Never join two dimensions directly to each other — join both to the fact instead.
• Snowflaking (Product → Category → Division) works but adds hops; flatten it in Power Query when you can.