PL-300 model the data explained
Model the data is worth 25–30% of PL-300 and is where most candidates lose the most marks. It covers designing the data model, writing calculations in DAX, and making the model fast. The questions describe tables and a requirement and ask which relationship, which expression or which setting satisfies it, so you need to reason about filter context rather than memorise syntax.
Design and implement a data model
Star schema and relationships
A good model is a star: one central fact table of events and dimension tables around it, joined by one-to-many relationships from dimension to fact. Filters flow from the one side to the many side by default, which is why a slicer on a dimension filters the fact table.
| Property | Typical setting | Why |
|---|---|---|
| Cardinality | One-to-many, dimension to fact | Dimension keys are unique |
| Cross-filter direction | Single | Bi-directional filters add ambiguity and slow queries |
| Active | One active relationship per pair of tables | Power BI will not allow two |
Many-to-many relationships and bi-directional filtering exist, but an exam scenario that offers them usually has a simpler star-schema answer.
Role-playing dimensions
When a fact table has several dates, such as order date and ship date, one date table plays several roles. Keep one relationship active and the others inactive, then activate an inactive one inside a measure with USERELATIONSHIP. The alternative is a separate copy of the date table for each role.
The date table
Time intelligence functions need a contiguous date table: one row per day, no gaps, covering every date in the fact data, with a unique date column that is marked as the date table. Create it with CALENDAR or CALENDARAUTO in DAX or with a query in Power Query. Turning off auto date/time and using one shared calendar is the recommended design.
Column and table properties
Set data types and formats, hide technical columns such as keys, set Sort by column (for example month name sorted by month number), set summarisation (do not sum a year or a price), and use categories such as city or URL.
Calculated columns versus measures
| Calculated column | Measure | |
|---|---|---|
| Evaluated | Row by row at refresh | At query time, in filter context |
| Stored | Yes, in the model | No |
| Use for | Slicer or axis values, relationships | Aggregations and ratios |
A ratio such as margin percentage must be a measure, because summing a per-row ratio in a total is wrong. A calculated table is a table built with a DAX expression, such as a date table or a table of distinct values.
Create model calculations by using DAX
CALCULATE
CALCULATE(expression, filter1, filter2, ...) evaluates an expression after changing the filter context. Filter arguments replace existing filters on the same column. ALL removes filters, ALLSELECTED keeps the filters the user applied through slicers and removes only those from the visual, KEEPFILTERS adds to existing filters rather than replacing them, and REMOVEFILTERS clears them explicitly.
A measure for a percent of total looks like DIVIDE([Total Sales], CALCULATE([Total Sales], ALL('Product'))). DIVIDE also handles division by zero.
Time intelligence
| Need | Function |
|---|---|
| Year to date | TOTALYTD, or CALCULATE with DATESYTD |
| Same period last year | SAMEPERIODLASTYEAR |
| Shift by an interval | DATEADD |
| Rolling window | DATESINPERIOD |
| Closing balance | CLOSINGBALANCEMONTH, or LASTDATE |
Semi-additive measures
Some values add across some dimensions but not across time: account balances, stock levels, headcount. Summing daily stock over a month is meaningless. A semi-additive measure takes the last value in the period, for example CALCULATE(SUM(Stock[Units]), LASTDATE('Date'[Date])).
Other items in the outline
- Quick measures write common DAX for you from the visual’s fields.
- Iterators such as
SUMXevaluate an expression per row and then aggregate. - Basic statistics:
AVERAGE,MEDIAN,MIN,MAX,COUNTROWS,DISTINCTCOUNT. - Calculation groups let one set of calculation items, such as current, prior year and year to date, apply to many measures, instead of writing a copy of each measure.
Optimize model performance
- Remove rows and columns you never use. Column count and cardinality drive model size.
- Reduce granularity: summarise to the level reports need, and split a datetime column into date and time or keep the date only.
- Use Performance Analyzer to see which visual is slow and how much time DAX, rendering and other work take, then paste the query into DAX query view to test measures.
- Prefer a star schema, single-direction relationships and measures over calculated columns where possible.
Sample questions
Question 1. A Customer table has a unique CustomerID. The Sales table has one row per order line and a CustomerID column. You create a relationship between them. Which cardinality and cross-filter direction follow best practice?
- A. Many-to-many with a bi-directional filter
- B. One-to-many with a bi-directional filter
- C. Many-to-one from Customer to Sales with a filter from Sales to Customer
- D. One-to-many from Customer to Sales with a single filter direction
Show answer
Answer: D
Customer has unique keys, so the relationship is one-to-many from Customer to Sales, with a single filter direction from the one side to the many side so that slicers on Customer filter Sales. Many-to-many is needed only when neither side is unique. Bi-directional filtering adds ambiguity and cost without a stated need.
Want more questions like this? Full PL-300 practice tests →
Question 2. A report must show sales year to date. The measure uses TOTALYTD against the Date column of a table with one row per day. The results are wrong for some years. Which requirement should you check first?
- A. The Date table is contiguous, has unique dates and is marked as a date table
- B. The Sales table uses DirectQuery
- C. The relationship from Date to Sales is bi-directional
- D. TOTALYTD is written as a calculated column
Show answer
Answer: A
Time intelligence needs a contiguous date table with unique dates, marked as the date table, covering every date in the data. Gaps or duplicates give wrong results. A calculated column does not fix a gap. DirectQuery and a bi-directional filter are not requirements for TOTALYTD.
Want more questions like this? Full PL-300 practice tests →
Question 3. A matrix shows margin percentage by product. A calculated column holds profit divided by sales for each row, and the matrix shows the sum of that column. The totals are wrong. What should you do?
- A. Change the summarisation of the column to Average
- B. Create a calculated table of margins
- C. Create a measure that divides total profit by total sales with DIVIDE
- D. Make the relationship bi-directional
Show answer
Answer: C
A ratio must be calculated from aggregated numerator and denominator at every level, which a measure does in the current filter context. Summing a per-row ratio gives meaningless totals. Changing the column to average gives the average of row ratios, still not total profit over total sales. A calculated table or relationship change does not solve it.
Want more questions like this? Full PL-300 practice tests →
What to practise
Build a small star schema from one fact table and three dimensions. Write each DAX measure above, then put it in a matrix with product, region and year and explain every number. Break the date table on purpose with a missing day and watch YTD go wrong. Create a second date relationship, make it inactive, and activate it with USERELATIONSHIP. Open Performance Analyzer and find your slowest visual. After that, scenario questions turn into recognising a situation you have already seen.