PL-300 Prepare the data Practice Question
You are building a Power BI semantic model that uses a large fact table from a data warehouse. The fact table has a date column and you want to create a date dimension. The organization requires that the date dimension includes all dates from 2010 to 2030, including weekends and holidays. What is the best practice for creating the date dimension?
⚠ Common exam trap
It's easy for candidates to choose Option C (DISTINCT on the fact table) thinking it is efficient, but they overlook that it will miss dates with no transactions, violating the requirement to include all weekends and holidays from 2010 to 2030.
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
✓
Create a date table in Power Query by generating a list of dates from 1/1/2010 to 12/31/2030 and then add columns for attributes
It follows the best practice of creating a dedicated date dimension table in Power Query, which ensures full control over the date range (2010–2030) and allows you to add custom attributes like holidays. This approach avoids relying on the fact table's date column, which may have gaps or missing dates, and ensures the date dimension is complete and independent for robust time intelligence calculations.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Use the CALENDAR function in DAX to generate the date range
Why it's wrong here
CALENDAR in DAX produces a one-column table of consecutive dates, but any additional attributes—such as holiday flags, fiscal periods, or weekday names—must be added as calculated columns using DAX expressions. This approach is less flexible than Power Query for incorporating external holiday calendars or complex date logic, and it often requires more DAX code for the same result. For a large semantic model, keeping transformation logic in M is generally more maintainable and performant.
- ✗
Mark the date column from the fact table as a date table and disable Auto Date/Time
Why it's wrong here
Marking the fact table's date column as a date table simply reuses that column for time intelligence, but it does not create a dedicated dimension that spans a full range of dates. Because the fact table's dates are limited to periods with actual transactions, you would lose calendar dates that have no data, which breaks continuous time intelligence like MTD or YTD calculations. Additionally, disabling Auto Date/Time is a separate setting; it doesn't solve the missing-dates problem or provide a robust foundation for holiday attributes.
- ✗
Create a date dimension by using DISTINCT on the fact table's date column
Why it's wrong here
Using DISTINCT to derive a date dimension from the fact table's date column builds the dimension only from dates that actually appear in the data, so any date with zero fact rows—including weekends, holidays, or future dates—will be absent. This makes the dimension incomplete and can produce incorrect results in measures that rely on a full calendar, such as rolling averages or day-over-day calculations. Moreover, the resulting table is not independent; changes to the fact table's filters or data refresh will alter the dimension itself.
- ✓
Create a date table in Power Query by generating a list of dates from 1/1/2010 to 12/31/2030 and then add columns for attributes
Why this is correct
Generating a complete list of dates in Power Query from 1/1/2010 to 12/31/2030 ensures that every day in that span exists as a row in the date table, regardless of activity in the fact table. You can then use M to add derived columns like year, month, quarter, ISO week number, and custom holiday flags by referencing a holidays table, making the solution flexible and easy to maintain. This approach follows the best practice of creating a distinct, static date dimension that supports reliable time intelligence and efficient relationships in a large semantic model.
Go deeper
Related to this question
About these practice questions
Courseiva writes every PL-300 question from scratch — 217 in total, each with an explanation and a wrong-answer breakdown. None are copied from real exams or 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.