Designing and Implementing a Data Model

Introduction

This lesson opens the domain Model the data (25–30% of the PL-300 skills measured, outline dated April 20, 2026). Power Query prepares the tables; the model decides how they relate and how users can filter and aggregate them. A good model makes DAX short and fast, and reports intuitive. A poor model produces wrong totals, ambiguous filters and slow visuals. The exam describes a business need and asks which relationship, property or table structure to choose. Microsoft does not publish the question count or the mix of question types; OnLevelUp mock exams use a 50-question practice length and an 80% practice target, neither of which is official or a conversion of the 700/1000 passing score.

After this lesson you will be able to configure table and column properties, define relationships with the correct cardinality and cross-filter direction, implement role-playing dimensions, build a common date table, and choose between calculated columns and calculated tables.

Learning objectives

• Configure table and column properties (data type, format, summarization, sort by column, data category, hidden fields, descriptions).
• Create and manage relationships: cardinality, cross-filter direction, active versus inactive.
• Implement role-playing dimensions with inactive relationships and USERELATIONSHIP.
• Create and mark a common date table.
• Decide when to use a calculated column, a calculated table or a Power Query step.

1. The model view and star schema

In Power BI Desktop, the Model view shows tables as boxes and relationships as lines with a 1 or * at each end and an arrow for the filter direction. The recommended design is the star schema from the previous lesson: fact tables in the middle, dimension tables around them. Filters flow from the one side (dimension) to the many side (fact).

Why it matters:

• A filter on DimProduct[Category] automatically reduces FactSales through the relationship, so a measure like SUM(FactSales[Amount]) responds correctly.
• Dimensions are small and have unique keys, so the engine resolves filters quickly.
• Facts hold only keys and numbers, which compress very well in the in-memory VertiPaq engine.

A snowflake (dimension linked to another dimension, such as Product → Subcategory → Category) works but adds relationship hops; flatten it in Power Query when practical.

2. Table and column properties

Select a column and use the Column tools ribbon or the Properties pane.

Details that regularly appear in questions:

• Numeric IDs (CustomerID 1001) default to Sum summarization; set them to Don't summarize or convert to text, or users will get meaningless totals.
• Sort by column needs a one-to-one relationship between the two columns (each month name maps to exactly one number).
• A geographic data category helps map visuals plot the right place; a column named State with values WA and OR is ambiguous without the category.
• Hiding a column does not delete it; relationships and DAX continue to work.
• Format strings change only the display. The underlying number keeps its precision.

Example. The DimDate table has MonthName (January...December) and MonthNumber (1...12). Without a sort-by setting, a column chart orders months alphabetically (April, August, December...). Setting MonthName to Sort by MonthNumber fixes the order across all visuals.

Setting properties in bulk. Select multiple columns in the Model view to change format, summarization or hidden status at once. This saves time when you hide all key columns in a model.

3. Relationships

A relationship connects one column in a table to one column in another. Power BI can autodetect relationships on load (matching column names and types), but you should always review them.

Cardinality

Power BI treats a column as the "one" side only if values are unique. A dimension key containing duplicates forces many-to-many or an error, which is why cleaning keys in Power Query matters. With many-to-many relationships, the "unmatched rows" and the way totals are computed can differ from what users expect; the preferred alternative is a bridge (junction) table of unique values related one-to-many to both sides.

Cross-filter direction

• Single: filters flow from the one side to the many side. Default and best for performance and clarity.
• Both (bidirectional): filters flow both ways. Use it for specific needs, for example a slicer that must show only customers who bought a selected product, or in a many-to-many pattern. Risks: ambiguous paths between tables, slower queries and unexpected results.

A rule worth memorising: use single-direction filtering by default; enable Both only for a clear requirement and prefer solving it with DAX (for example CROSSFILTER inside a measure) or a different design. Also, a security rule (RLS) filter follows the relationship direction unless Apply security filter in both directions is selected.

Active versus inactive relationships

Only one active relationship can exist between two tables at a time; others are inactive (dashed line). A filter propagates only through active relationships. Inactive ones are activated for a single measure with USERELATIONSHIP. The same applies to ambiguity: two active paths between the same pair of tables (directly or indirectly) are not allowed.

Referential integrity

When the many side contains a key that has no match on the one side, Power BI shows those rows under a blank member in the dimension. A blank group in a chart is a symptom of missing dimension rows; fix with an "Unknown" dimension row or by correcting the source.

4. Role-playing dimensions

A role-playing dimension is a single dimension used in several roles. The classic case: FactSales has OrderDate, ShipDate and DueDate, all related to one DimDate table.

Two ways to implement it:

1. One date table with one active and several inactive relationships. OrderDate → DimDate[Date] is active; ShipDate and DueDate are inactive. A measure uses USERELATIONSHIP:

Sales by Ship Date =
CALCULATE ( [Total Sales], USERELATIONSHIP ( FactSales[ShipDate], DimDate[Date] ) )

2. Several copies of the date table (DimOrderDate, DimShipDate), each with an active relationship. Slicers can use each date independently, at the cost of more tables. Create copies with Power Query (reference) or as calculated tables.

Choose approach 1 when most reports use the main date and the others are occasional, and approach 2 when users must slice by two dates at once on the same page.

Worked example 1: sales by order date and by ship date. Scenario. Adventure Outlet has FactOrders[OrderDate] and FactOrders[ShipDate], both Date columns, and one DimDate. Management wants a column chart of revenue by month of order and another showing shipped revenue by month, using the same Month axis and slicer.

Reasoning.

1. Create the relationship FactOrders[OrderDate] → DimDate[Date] (active).
2. Create FactOrders[ShipDate] → DimDate[Date]; Power BI makes it inactive automatically because an active one exists.
3. Create [Revenue] = SUM ( FactOrders[Amount] ) and [Shipped Revenue] = CALCULATE ( [Revenue], USERELATIONSHIP ( FactOrders[ShipDate], DimDate[Date] ) ).
4. Use DimDate[Month] on the axis; the first visual uses [Revenue], the second [Shipped Revenue].

Why alternatives are worse. Making both relationships active is not allowed (ambiguity). Creating a second set of month columns in the fact table duplicates logic. Using bidirectional filtering does not select which date is used; it only changes the direction of propagation.

5. The common date table

Time intelligence functions (year to date, same period last year) need a date table: a table with one row per day, contiguous, without gaps, covering the full range of the data (from 1 January of the first year to 31 December of the last year), with a Date column of type Date and unique values.

You can create it in three ways:

Example in DAX:

DimDate =
ADDCOLUMNS (
    CALENDAR ( DATE ( 2022, 1, 1 ), DATE ( 2027, 12, 31 ) ),
    "Year", YEAR ( [Date] ),
    "Month Number", MONTH ( [Date] ),
    "Month", FORMAT ( [Date], "mmm" ),
    "Quarter", "Q" & QUARTER ( [Date] ),
    "Year-Month", FORMAT ( [Date], "yyyy-MM" )
)

After creating it:

1. Relate DimDate[Date] to each fact date column (one-to-many).
2. Mark as date table (Table tools → Mark as date table) and choose the Date column. Power BI validates that it is unique, with no blanks and no gaps. This also disables the hidden auto date/time tables for that relationship and makes DAX time-intelligence functions behave correctly with ALL ( DimDate ).
3. Set Sort by column for month names and hide technical columns.
4. Turn off Auto date/time (File → Options → Data Load) in large models; it creates a hidden date table for every date column and grows the model.

Common pitfalls. A fact date that contains time (2025-03-14 10:23) does not match DimDate[Date], which is at midnight. Convert the fact column to Date in Power Query, or split date and time. Another trap is a date table that stops at the last sale date: forecasts or future budgets then fall outside it.

6. Calculated columns, calculated tables and measures

Guidance:

• Prefer measures for anything you aggregate: ratios, running totals, YTD.
• Use a calculated column when you need the value for slicing, sorting or relationships, such as a customer's segment label based on their lifetime purchase, or RELATED values.
• Use a calculated table for tables that do not exist in the source (a date table, a table of unique values for a bridge).
• Prefer Power Query columns over calculated columns when the logic uses only the row's own source data; they load faster and may fold to the source.
• Columns increase model size; measures do not. A calculated column that concatenates strings over 50 million rows can bloat the model.

Example. To classify customers as 'VIP' when their total purchases exceed 10,000, a calculated column on DimCustomer such as VIP = IF ( CALCULATE ( SUM ( FactSales[Amount] ) ) > 10000, "VIP", "Regular" ) allows a slicer on VIP. A measure cannot be used on a slicer axis. Note that the column is computed at refresh and does not respond to report filters.

7. Practical modelling checklist

1. Build facts and dimensions with unique dimension keys.
2. Set the correct data types and Don't summarize on keys and identifiers.
3. Create relationships (one-to-many, single direction), review autodetected ones and remove accidental ones.
4. Add a marked date table, relate it to each date column, and use inactive relationships for secondary dates.
5. Hide foreign keys, helper columns and unused columns; use display folders for measures.
6. Add formats, descriptions and synonyms.
7. Test with a simple visual: totals should match the source, and slicers should filter as expected.

8. Pitfalls and exam tips

• Bidirectional everywhere is a classic mistake: it produces ambiguity and slow reports. Choose single direction unless there is a specific need.
• Many-to-many to avoid cleaning a key is a design smell. Fix the key or use a bridge table.
• A blank row in visuals indicates fact keys with no dimension match.
• Sort by column needs a matching one-to-one numeric column.
• Marking a date table requires a unique, gap-free Date column; if validation fails, check for duplicates or missing days.
• The fact table's date must be Date, not Date/Time, to relate to a day-level date table.
• Exam tip: "A user needs to analyse sales by order date and by ship date using one date table" → one active plus one inactive relationship and USERELATIONSHIP. "Both dates must be sliced independently on one page" → separate date tables.
• Exam tip: "Show months in calendar order" → Sort by column. "Show cities on a map accurately" → data category.
• Exam tip: "Total of IDs appears in a visual" → change summarization to Don't summarize.

9. Mini case study

Case. Wingtip Bikes has these tables: Sales (OrderID, OrderDate, ShipDate, ProductID, CustomerID, Qty, Amount), Products (ProductID, Name, Category, SubcategoryID), Subcategories (SubcategoryID, Subcategory, Category), Customers (CustomerID, Name, City, State) and Calendar (Date, Year, MonthName, MonthNumber). On load, Power BI created relationships: Sales[OrderDate]→Calendar[Date], Sales[ShipDate]→Calendar[Date] (inactive), Sales[ProductID]→Products[ProductID], Products[SubcategoryID]→Subcategories[SubcategoryID] and Customers[CustomerID]→Sales[CustomerID] set to Both. Visuals show months out of order, a blank category, and the sum of CustomerID. A manager asks for a "Shipped Units by Month" chart.

Tasks. Fix the model and meet the request.

Solution.

1. Months: set MonthName to Sort by column MonthNumber.
2. Blank category: a Sales row has a ProductID that does not exist in Products. Use Power Query to find orphans (Left Anti), then fix the source or add an "Unknown" product row.
3. Sum of CustomerID: set its summarization to Don't summarize (or convert to Text) and hide it.
4. Relationships: change the Customers–Sales filter to Single unless a specific bidirectional need is documented. Flatten the snowflake: merge Subcategories into Products in Power Query so slicers by category and subcategory use a single table.
5. Date table: mark Calendar as the date table after verifying it covers every order and ship date; confirm that OrderDate and ShipDate are Date (not Date/Time).
6. Request: keep OrderDate active and create Shipped Units = CALCULATE ( SUM ( Sales[Qty] ), USERELATIONSHIP ( Sales[ShipDate], Calendar[Date] ) ). Use Calendar[MonthName] on the axis.

Commentary. Every symptom maps to one property or relationship setting. The fix keeps a single active date relationship, which avoids ambiguity, and uses a measure for the secondary date, avoiding a second date table that is not required here.

Key terms

• Star schema: facts surrounded by dimensions.
• Cardinality: one-to-many, one-to-one or many-to-many relationship type.
• Cross-filter direction: direction in which filters propagate: single or both.
• Active / inactive relationship: the one used by default versus one activated with USERELATIONSHIP.
• Role-playing dimension: one dimension related to a fact in several roles.
• Date table: contiguous daily table marked as the date table.
• Sort by column: property ordering one column by another.
• Calculated column / calculated table / measure: DAX objects evaluated at refresh (first two) or at query time (measure).
• Bridge table: table of unique keys resolving many-to-many.

Summary checklist

• I can set summarization, format, sort by column and data category.
• I can pick cardinality and cross-filter direction and justify single direction.
• I can implement a role-playing date with USERELATIONSHIP or separate date tables.
• I can create, relate and mark a gap-free date table.
• I can decide between a Power Query column, a calculated column, a calculated table and a measure.

Official sources

• Study guide for Exam PL-300: https://learn.microsoft.com/en-us/credentials/certifications/resources/study-guides/pl-300 (opens in a new tab)
• Create and manage relationships in Power BI Desktop: https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-create-and-manage-relationships (opens in a new tab)
• Understand star schema and the importance for Power BI: https://learn.microsoft.com/en-us/power-bi/guidance/star-schema (opens in a new tab)
• Set and use date tables in Power BI Desktop: https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-date-tables (opens in a new tab)
• Active vs inactive relationship guidance: https://learn.microsoft.com/en-us/power-bi/guidance/relationships-active-inactive (opens in a new tab)
• Bidirectional relationship guidance: https://learn.microsoft.com/en-us/power-bi/guidance/relationships-bidirectional-filtering (opens in a new tab)