What's changed: Created PL-300 Chapter 2 (domain: Model the data): data model design (star schema, table/column properties, role-playing dimensions, cardinality/cross-filter direction, date table, calculated columns/tables), DAX calculations (measures vs calculated columns, CALCULATE, time intelligence, semi-additive, quick measures, calculation groups), and model performance optimization (remove unnecessary rows/columns, Performance Analyzer/DAX query view, reduce granularity).
2.1Design a data model
Understand star schema, table/column properties, role-playing dimensions, relationship cardinality and cross-filter direction, a common date table, and calculated columns/tables.
Good reports come from a good model. Build on a star schema and design relationships correctly.
2.1.1Star schema and properties
Power BI is best with a star schema: a central fact table surrounded by dimension tables. Set table/column properties (data type, format, default aggregation, data category, sort-by column) to tune behavior. When one dimension serves multiple roles (e.g., order date and ship date both reference the date table), treat it as a role-playing dimension and switch inactive relationships via USERELATIONSHIP.
2.1.2Relationships
Relationships have cardinality, usually one-to-many (1:many) (one dimension row to many fact rows). Use many-to-many only when unavoidable, with bridge tables or careful design. Cross-filter direction defaults to single (dimension→fact, one-way), which is safe; use both only when needed, mindful of ambiguity and circular risks.
2.1.3Date table and calculated columns/tables
Time intelligence (YTD, etc.) needs a contiguous common date table, used by "Mark as date table." A calculated column stores a per-row value in row context (usable on slicers/axes; increases model size); a calculated table generates a table via DAX (useful for date tables or role-playing copies). Leave value aggregation to measures and avoid overusing calculated columns.
Cues: "central numbers + surrounding attributes" = star schema. "one row→many rows" = one-to-many. "safe default direction" = single cross-filter. "propagate both ways" = bidirectional (only when needed). "same dimension, multiple roles" = role-playing (USERELATIONSHIP). "for YTD" = Mark as date table. "store per-row value" = calculated column.
Watch the mix-ups: (1) Bidirectional cross-filter invites ambiguity/cycles—default to single. (2) Do not casually use many-to-many. (3) Do not confuse calculated columns (per-row, larger model) with measures (aggregation, dynamic). (4) Time intelligence requires a date table.
2.1.4Section summary
- Star schema (fact+dimension) is the base; one-to-many is the usual cardinality
- Single cross-filter by default; both and many-to-many only when needed, carefully
- Mark as date table for YTD; role-playing via USERELATIONSHIP
Sign in to track progress — Log in.
Quick check
(just a quick review)Q1. You want a model of a sales fact with product/date/customer dimensions. Recommended design?
Q2. One date-dimension row maps to many sales-fact rows. What is the relationship cardinality?
Q3. To avoid ambiguity and circular dependencies, which cross-filter direction is the safe default?
Q4. You want to analyze both order date and ship date against the same date table. Best approach?
Q5. What is required to correctly use time intelligence functions like YTD?