Model the data Study Guide — PL-300

Model the Data

This domain accounts for 25–30% of the official PL-300 exam (our practice allocation: 27%). It tests your ability to design efficient semantic models in Power BI Desktop, write correct DAX expressions, configure relationships, enforce row-level security, and optimize model performance. Questions range from conceptual ("why use a star schema?") to code-reading ("what does this DAX return?") to scenario-based ("which approach reduces model size?"). Mastery here directly determines whether you pass.

Star Schema Design: Fact and Dimension Tables

A star schema organizes data into fact tables (measurements, events, transactions) and dimension tables (descriptive context). The fact table sits at the center; dimension tables radiate outward like points on a star.

Snowflake schema normalizes dimension tables into sub-dimensions (e.g., Product → Subcategory → Category). Power BI supports this but star schema is preferred because it reduces the number of relationships, simplifies DAX, and improves VertiPaq compression.

Slowly Changing Dimensions (SCDs):

• Type 1: Overwrite old value — no history preserved.
• Type 2: Add a new row with a new surrogate key — full history preserved. Used when you need to report "what was the customer's region at the time of sale?"

Common traps:

• Exam scenarios describe a normalized source and ask what to do — the answer is almost always "flatten into a star schema in Power Query before loading."
• Confusing grain: a fact table must have one consistent grain (e.g., one row per order line, not mixed order-level and line-level rows).
• Using a single large table instead of separating facts from dimensions is a distractor for performance questions — always separate them.

Worked scenario: A company has a Sales table joined to a Subcategory table joined to a Category table. Reports are slow. The correct fix is to merge Subcategory and Category into the Product dimension table in Power Query, producing a single flat dimension — not to keep the snowflake structure in the model.

Relationships: Cardinality, Cross-Filter Direction, and Inactive Relationships

Bold the decision tree concept — this diagram shows how to configure a relationship.

Relationship configuration decision flow

Cardinality Types

Cross-Filter Direction

• Single (default): Filters propagate from the "one" side to the "many" side. Dimension → Fact.
• Both: Filters propagate in both directions. Required for many-to-many patterns but introduces risk of ambiguous paths and unexpected filter propagation.
• Use Both only when necessary. The exam tests whether you know that Both can cause incorrect totals if not carefully designed.

Inactive Relationships

A model can have multiple relationships between two tables, but only one can be active. Inactive relationships exist but are ignored by default. Use USERELATIONSHIP in DAX to activate an inactive relationship within a specific measure calculation.

Common traps:

• You cannot have two active relationships between the same pair of tables — enabling a second one automatically deactivates the first.
• Many-to-many with "Both" cross-filter is often a distractor; the cleaner solution is a bridge table with two 1-to-many relationships.
• Cross-filter direction "Both" does not mean bidirectional security — RLS still follows relationship direction.
• A relationship on a column with blanks/nulls on the many side is valid, but nulls on the one-side key cause relationship errors.

Worked scenario: A date table must relate to Sales on OrderDate (active) and ShipDate (inactive). To calculate shipping analysis, create a measure using USERELATIONSHIP(Sales[ShipDate], Date[Date]) inside CALCULATE. Do not create a second date table — use inactive relationships.