PL-300: Design a Power BI Data Model
Build a model that filters and totals correctly
A report can show the wrong total even when every source row is correct. Power BI data model design starts with the grain of each fact table, unique dimension keys, and clear filter paths. The model then determines how dates, categories, and customer segments behave in a report.
This lesson covers Design and implement a data model in the PL-300 Model the data domain (25–30% of the exam, skills measured from April 20, 2026). You will set column properties, choose relationship cardinality and filter direction, build a shared date table, and decide where calculations belong.
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. A star schema places facts in the middle and dimensions around them. With a regular one-to-many relationship and single-direction filtering, dimensions filter facts. If your source is a flat export, start by building fact and dimension tables in Power Query.

Why it matters:
- A filter on
DimProduct[Category]automatically reducesFactSalesthrough the relationship, so a measure likeSUM(FactSales[Amount])responds correctly. - Dimensions are small and have unique keys, so the engine resolves filters quickly.
- Facts commonly hold dimension keys and numeric values at a consistent grain. Keep attributes that users filter by in appropriate dimensions. In Import, cardinality and column types influence compression.
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.
| Property | What it does | Example |
|---|---|---|
| Data type | Controls storage and calculations | Whole Number for Quantity, Date for OrderDate |
| Format | Display format only, not stored value | $#,0.00, 0.0%, dd mmm yyyy |
| Summarize by | Default aggregation when the column is dropped on a visual | Sum for Amount; Don't summarize for Year or an ID |
| Sort by column | Sorts text by another column | MonthName sorted by MonthNumber so January comes before April |
| Data category | Tells visuals how to interpret text | City, Country/Region, Web URL, Image URL, Latitude |
| Hidden | Hides the field from the report canvas (still usable in DAX and relationships) | Hide foreign keys and technical columns |
| Description | Tooltip for report authors | "Net amount after discounts, excluding tax" |
| Display folder | Groups fields in the Data pane | Measures\Time Intelligence |
| Key column (table) | Marks a unique column in the table | Used by some features |
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 requires each displayed value to map to a single sort value at the same grain. The columns belong to the same table; no model relationship is needed.
- A geographic data category helps map visuals plot the right place; a category can help identify a field as state or province. When place names are ambiguous, include country/region context or use verified latitude and longitude.
- Hiding a column tidies the field list. It does not enforce security, and relationships and DAX can still use it.
- 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 defines the month order when a visual sorts by MonthName. A visual can still sort by a measure instead. Use a year-month field for a chronological axis spanning years.
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 propose relationships during load. Review which columns it paired, the cardinality, and the resulting filter direction.
Cardinality
| Cardinality | Meaning | Typical use |
|---|---|---|
| One-to-many (1:*) | One side has unique values, many side repeats | Dimension to fact. The default and recommended |
| Many-to-one (*:1) | Same as above, reversed viewpoint | Fact to dimension |
| One-to-one (1:1) | Both columns unique | Splitting a wide table into two, or extension tables; consider merging instead |
| Many-to-many (*:*) | Neither column unique | Bridge scenarios with non-unique keys; use with care, results can surprise |
Power BI treats a column as the "one" side only if values are unique. A dimension key with duplicates cannot be the one side. Fix the grain or the key if the business relationship is one-to-many. For a true many-to-many relationship between two dimensions, a bridge usually stores one row per valid pair of keys. Relate each dimension’s unique key to the corresponding repeating key in the bridge and verify the intended filter path.
![New relationship from Sales[ProductID] to Products[ProductID]: Many to one, Single cross-filter direction, active](/media/certifications/data-analytics/power-bi-data-analyst/screens/04-design-data-model/c04-new-relationship-many-to-one.webp)
Cross-filter direction
- Single: on a one-to-many relationship, filters flow from the one side to the many side. Use this as the usual starting point for dimension-to-fact relationships.
- 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.
Use Both for a specific need after testing ambiguity and totals. If only one calculation needs reverse filtering, consider CROSSFILTER inside that measure. For a slicer that should show only values with data, a visual-level measure filter can be a better fit. RLS propagation is separate: it uses active relationships, and bidirectional security requires the applicable Apply security filter in both directions setting. Test the roles explicitly.
Active versus inactive relationships
Only one active relationship can exist between two tables at a time; others are inactive (dashed line). Active relationships provide the default filter path. USERELATIONSHIP can use an existing alternative relationship for a calculation. Multiple paths can cause ambiguity; review the routes rather than enabling extra paths indiscriminately. RLS does not propagate through an inactive relationship even when a measure uses USERELATIONSHIP. The function also has documented restrictions in RLS contexts.
![New relationship from Sales[ShipDate] to Date[Date]: Power BI leaves Make this relationship active unticked because OrderDate is already related](/media/certifications/data-analytics/power-bi-data-analyst/screens/04-design-data-model/c05-inactive-shipdate-relationship.webp)

Referential integrity
With a regular one-to-many relationship, Power BI can add a virtual blank member on the dimension side for unmatched fact keys. A blank category may also come from an actual missing attribute. Check both. Fix the source or map unmatched fact keys to an explicit Unknown dimension key; adding the dimension row alone does not remap facts. Limited relationships behave differently.
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:
- One date table with one active and several inactive relationships.
OrderDate → DimDate[Date]is active;ShipDateandDueDateare inactive. A measure usesUSERELATIONSHIP:
Sales by Ship Date =
CALCULATE (
[Total Sales],
USERELATIONSHIP ( FactSales[ShipDate], DimDate[Date] ),
KEEPFILTERS ( NOT ISBLANK ( FactSales[ShipDate] ) )
)- 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 for occasional alternate-date calculations when no security filter needs the inactive path. Choose approach 2 for independent slicing or when the roles need active filter paths. Define and test RLS for the resulting model.
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 ordered revenue and shipped revenue by month using the same date axis and slicer. Unshipped rows have a blank ShipDate; the shipped measure must exclude them, including in the grand total.
Reasoning.
- Create the relationship
FactOrders[OrderDate]→DimDate[Date](active). - Create
FactOrders[ShipDate]→DimDate[Date]; leave it inactive while the OrderDate relationship is active. - Create
[Revenue] = SUM ( FactOrders[Amount] )andShipped Revenue = CALCULATE ( [Revenue], USERELATIONSHIP ( FactOrders[ShipDate], DimDate[Date] ), KEEPFILTERS ( NOT ISBLANK ( FactOrders[ShipDate] ) ) ).KEEPFILTERSpreserves narrower fact-date filters while excluding blank shipment dates. - Use a year-month field from
DimDateon the axis (or Month with a single-year filter); 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
For classic DAX time intelligence, use a dedicated date table with one unique, non-null row per day, no gaps, and coverage for the required full calendar or fiscal years. Use a Date key for day-level relationships. A Date/Time key can pass date-table validation if every value uses the same timestamp. Microsoft also documents calendar-based time intelligence as a preview with separate calendar configuration. The example below uses classic time intelligence.
You can create it in three ways:
| Method | Notes |
|---|---|
DAX calculated table with CALENDAR or CALENDARAUTO |
Quick; CALENDARAUTO uses eligible model date columns and returns full years; check the generated range |
Power Query (M, List.Dates) |
Loads at refresh; can be shared in a dataflow |
| From the source / shared semantic model | One table for the organisation, best for consistency |
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:
- Create a one-to-many relationship from
DimDate[Date]to each fact’s primary date. Use inactive secondary relationships or separate role-playing date tables where appropriate. - Mark as date table (Table tools → Mark as date table) and choose the Date column. Power BI validates uniqueness, no nulls, contiguous dates, and a consistent timestamp for Date/Time. Review any visuals using an automatic date hierarchy when switching to an explicit table.
- Set Sort by column for month names and hide technical columns.
- Turn off Auto date/time (File → Options → Data Load) in large models; it can create hidden tables for eligible Import date columns. Check visuals that rely on those hierarchies before disabling it.


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
| Object (Import model) | When and where | Typical use | Storage | Recalculates for slicers? |
|---|---|---|---|---|
| Power Query column | At refresh, in the query engine (possibly the source) | Row-level, before load | Stored, compressed | No |
| DAX calculated column | At refresh, in the model, row by row | Row-level logic needing other tables or model relationships | Stored in the model | No; computed during processing |
| DAX calculated table | At refresh | Date tables, bridge tables, role-playing copies, summary tables | Stored | No |
| Measure | At query time | Aggregations that depend on filter context | Result calculated as needed | Yes |
Guidance:
- Use explicit DAX measures and CALCULATE for filter-responsive totals, ratios, and time-based calculations. The table describes Import behavior; check DirectQuery restrictions before using model calculations there.
- 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
RELATEDvalues. - 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 may fold and run in the source. Check refresh performance rather than assuming one approach is always faster.
- Columns increase model size; measures do not. A calculated column that concatenates strings over 50 million rows can bloat the model.
![A DAX calculated column LineMargin = Sales[SalesAmount] - Sales[Quantity] * RELATED ( Products[UnitCost] )](/media/certifications/data-analytics/power-bi-data-analyst/screens/04-design-data-model/c09-calculated-column-dax.webp)
![A DAX measure Total Sales = SUM ( Sales[SalesAmount] )](/media/certifications/data-analytics/power-bi-data-analyst/screens/04-design-data-model/c10-measure-dax.webp)
![]()
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
- Build facts and dimensions with unique dimension keys.
- Set the correct data types and Don't summarize on keys and identifiers.
- Create relationships (one-to-many, single direction), review autodetected ones and remove accidental ones.
- Add a marked date table, relate it to each date column, and use inactive relationships for secondary dates.
- Hide foreign keys, helper columns and unused columns; use display folders for measures.
- Add formats, descriptions and synonyms.
- 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 group can indicate unmatched fact keys or missing dimension attributes; inspect the relationship and data before fixing it.
- Sort by column needs one sort value per displayed value; the month-number example uses a numeric sort key.
- Marking a date table requires a unique, gap-free Date column; if validation fails, check for duplicates or missing days.
- Day-level relationship keys must match in type and value. Strip the time component in Power Query when the dimension key represents midnight and fact timestamps vary.
- 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.
- Months: set
MonthNameto Sort by columnMonthNumber. - Blank category: check for missing Category values and use a Left Anti merge to find sales whose product key is absent, then fix the source or remap the unmatched sales to the key of an explicit Unknown product row.
- Sum of
CustomerID: set its summarization to Don't summarize (or convert to Text) and hide it. - Relationships: change the Customers–Sales filter to Single unless a specific bidirectional need is documented. Flatten the snowflake: merge
SubcategoriesintoProductsin Power Query so slicers by category and subcategory use a single table. - Date table: mark
Calendaras the date table after verifying it covers every order and ship date; confirm thatOrderDateandShipDateare Date (not Date/Time). - Request: keep OrderDate active and create
Shipped Units = CALCULATE ( SUM ( Sales[Qty] ), USERELATIONSHIP ( Sales[ShipDate], Calendar[Date] ), KEEPFILTERS ( NOT ISBLANK ( Sales[ShipDate] ) ) ). Use a year-month field or filter to one year when usingCalendar[MonthName].
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 valid key pairs that represents a many-to-many business association.
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
USERELATIONSHIPor 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)
-
Microsoft Learn, model relationships and many-to-many design: https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-relationships-understand (opens in a new tab) and https://learn.microsoft.com/en-us/power-bi/guidance/relationships-many-to-many (opens in a new tab)
-
Microsoft Learn, USERELATIONSHIP: https://learn.microsoft.com/en-us/dax/userelationship-function-dax (opens in a new tab)
-
Microsoft Learn, sort by column and time intelligence: https://learn.microsoft.com/en-us/power-bi/create-reports/desktop-sort-by-column (opens in a new tab) and https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-time-intelligence (opens in a new tab)
-
Microsoft Learn, automatic date tables and calculation types: https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-auto-date-time (opens in a new tab) and https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-calculations-options (opens in a new tab)
