PL-300 Prepare the data Practice Question
You are designing a data model in Power BI. You have a 'Sales' table and a 'Date' table. The 'Sales' table has a 'SalesDate' column of type Date. You need to create a relationship between the tables, but the 'Date' table contains dates from 2010 to 2025, while the 'Sales' table only has data from 2020. Which type of relationship should you create to ensure optimal performance and correct filtering?
⚠ Common exam trap
Many exam-takers assume bi-directional filtering is needed for correct filtering, but in a star schema, single-direction filtering from the dimension to the fact table is both sufficient and optimal for performance.
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
✓
Many-to-one with single cross-filter direction from Date to Sales
A many-to-one relationship with single cross-filter direction from Date to Sales ensures that filters applied to the Date table propagate to the Sales table, which is the standard star schema design. This configuration optimizes query performance by avoiding unnecessary bi-directional filtering and correctly handles the date range mismatch, as the Date table's larger range does not affect filtering of Sales data.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Many-to-one with bi-directional cross-filtering
Why it's wrong here
While the cardinality is correctly many-to-one, enabling bi-directional cross-filtering makes filters flow both ways between Date and Sales. This can introduce ambiguity when other relationship paths exist between tables, potentially causing incorrect aggregations or circular dependency errors, and it degrades performance by propagating filters redundantly. Since time intelligence only requires filters to flow from the dimension to the fact, bi-directionality is unnecessary and harmful in this context.
- ✓
Many-to-one with single cross-filter direction from Date to Sales
Why this is correct
This is the correct modeling pattern for a star schema: the Date table contains unique dates, while Sales has multiple rows for each date, so the relationship is many-to-one. With single cross-filter direction from Date to Sales, filtering by a date or a date hierarchy propagates to sales transactions, enabling efficient time-based aggregations. This unidirectional flow is unambiguous, standard, and performs well because it minimizes the filter context path.
- ✗
One-to-one (1:1)
Why it's wrong here
A one-to-one relationship would require that each date maps to at most one sale and each sale maps to exactly one date, but in reality multiple sales transactions occur on the same date. Thus, the cardinality between Date and Sales is one-to-many (or many-to-one depending on direction), not one-to-one. Even if only one sales row existed per date, 1:1 is typically used for row-level security or splitting tables, not for relating a dimension to a fact table.
- ✗
Many-to-many (M:M)
Why it's wrong here
A many-to-many relationship would imply that a single sale can have multiple dates and a single date can have multiple sales, which is not the case here; each sale is recorded on exactly one date, while each date can have many sales. The appropriate cardinality is one-to-many from Date to Sales, or many-to-one from Sales to Date, not many-to-many. Implementing M:M would require a bridge table and would complicate the model without any benefit, as the real-world grain is a fact with a single date foreign key.
Go deeper
Related to this question
About these practice questions
One of 524 original PL-300 practice questions on Courseiva, each with a full explanation and wrong-answer analysis — not exam dumps or protected exam content. 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.