PL-300 Model the data Practice Question
You are building a star schema model in Power BI. You have a fact table of sales transactions and dimension tables for Date, Customer, Product, and Store. The Date table contains a column 'FiscalYear' that you want to use for time intelligence calculations. What is the best practice for handling the Date relationship?
⚠ Common exam trap
Many exam-takers think they need a separate fiscal table or composite keys to handle fiscal years, but Power BI's date table marking feature inherently supports fiscal calendars through the 'Mark as Date Table' option and the 'Start of Fiscal Year' setting, making those workarounds unnecessary and incorrect.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
Mark the Date table as a date table using the Calendar icon in the Table tools ribbon and set a relationship on the Date column.
Marking the Date table as a date table (via the Calendar icon in Table tools) and creating a relationship on the Date column is the best practice for time intelligence in Power BI. This ensures that DAX time intelligence functions (e.g., TOTALYTD, SAMEPERIODLASTYEAR) work correctly by using a single, continuous date column that aligns with the fact table's date column. It also avoids the need for composite keys or separate fiscal tables, maintaining a clean star schema.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Create a separate fiscal date table and relate it to the fact table using the FiscalYear column.
Why it's wrong here
Relating a separate fiscal date table to the fact table using the FiscalYear column is incorrect because FiscalYear alone does not provide a day-level grain; it repeats across multiple dates, leading to a many-to-many relationship that cannot be used for typical star schema filtering. Additionally, Power BI time intelligence functions require a single date column marked as a date table, and a separate fiscal-year table without a contiguous date column cannot be marked as such, so it only adds unnecessary model complexity without enabling correct time-based analysis.
- ✗
Use the built-in DATESYTD function directly on the OrderDate column from the fact table.
Why it's wrong here
Using the built-in DATESYTD function directly on the OrderDate column from the fact table is problematic because time intelligence functions in DAX expect a date column from a properly marked date table, not a fact table's transaction date. Fact tables contain order dates that may have gaps or duplicates, and without a separate date dimension marked as a date table, DATESYTD cannot establish the correct year-to-date period or propagate filters reliably across other dimensions. This violates the star schema separation of measures and dimensions, leading to incorrect or unpredictable results when multiple filters are applied.
- ✗
Create a composite key using FiscalYear and Quarter columns in the Date table and relate to the fact table.
Why it's wrong here
Creating a composite key using FiscalYear and Quarter columns in the Date table fails because these columns do not uniquely identify individual dates—each quarter contains many date rows, so the relationship cannot be resolved as a one-to-many from the date dimension to the fact table. Power BI relationships require a single unique column on the dimension side, and composite keys are not supported directly in the relationship dialog; you would need a concatenated column, but even then it would not be a date column, preventing the table from being marked as a date table and disabling time intelligence functions. The standard approach is to use a single Date column as the primary key and relationship column.
- ✓
Mark the Date table as a date table using the Calendar icon in the Table tools ribbon and set a relationship on the Date column.
Why this is correct
Marking the Date table as a date table using the Calendar icon in the Table tools ribbon is the correct approach because it explicitly identifies the Date column as the continuous set of dates that Power BI uses to enable time intelligence functions like DATESYTD, TOTALYTD, and SAMEPERIODLASTYEAR. Setting a relationship on the Date column—which is unique and contiguous—ensures proper filtering from the date dimension to the fact table, following the star schema design principle. This allows DAX calculations to correctly respect the user's selected date range and fiscal calendar, making it the only option that fully supports robust time-based reporting.
Go deeper
Related to this question
About these practice questions
This PL-300 question is part of Courseiva's 217-question bank — original exam-style content with full explanations and wrong-answer analysis, never real exam questions or exam dumps. Learn why practice questions differ from exam dumps →
JA
Written by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
This PL-300 practice question is part of Courseiva's free Microsoft certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the PL-300 exam.