PL-300 Star Schema Practice Question
You are designing a Power BI semantic model for a retail company. The model includes a Sales table (50 million rows) and a Product table (10,000 rows). You need to create a measure that calculates the average sales amount per product category. The Product table has a column 'Category' with 20 distinct values. To optimize performance, what should you do?
⚠ Common exam trap
Candidates often default to adding calculated columns or summarizing tables, but the most performant approach in Power BI is to use relationships and let the engine handle aggregation dynamically.
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 relationship between Sales and Product on ProductID, and use Product[Category] in the measure.
Option D is correct because creating a relationship between Sales and Product on ProductID and then using Product[Category] in the measure lets the VertiPaq engine aggregate the 50 million Sales rows through the dimension, which is the standard star-schema approach and performs best. The relationship filters Sales by category at query time, so no row-level lookups or duplicated category values are stored in the large fact table. Option A is wrong because a calculated column using RELATED materializes the category on all 50 million Sales rows, increasing model size and refresh time. Option B is wrong because summarizing Sales to category level in Power Query destroys the detail needed for other measures and prevents dynamic slicing. Option C is wrong because a separate calculated table would not be related to Sales on ProductID and would not correctly filter the fact table.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Add a calculated column to Sales that looks up the category using RELATED.
Why it's wrong here
Adding a calculated column to Sales that uses RELATED to look up Category stores a duplicate category value for every row in the 50-million-row fact table, causing significant storage overhead and longer refresh times. Because Power BI evaluates calculated columns row-by-row during data load, it bypasses the efficiency of relationships and consumes memory for a value that can be retrieved dynamically at query time. This redundancy is unnecessary and degrades model performance, particularly at high volume, so it should be avoided in favor of a relationship.
- ✗
Summarize the Sales table to the category level in Power Query.
Why it's wrong here
Summarizing the Sales table to the category level in Power Query permanently loses transaction-level granularity, so measures requiring filters on date, product, or customer cannot be computed accurately. VertiPaq's columnar compression is designed to handle large fact tables, and pre-aggregation prevents the engine from using its efficient in-memory storage and forces you to manage a separate, brittle summary table that must be refreshed alongside the base data. This approach sacrifices drilldown and analysis flexibility, so it is not a valid optimization.
- ✗
Create a separate calculated table for categories and link it to Sales.
Why it's wrong here
Creating a separate calculated table for categories and linking it directly to Sales is redundant because the Product table already contains the category attribute and is the correct source in a star schema. For that category table to filter Sales directly, you would need to add a category key to the fact table, which denormalizes your model and repeats the storage overhead; alternatively, linking it through Product exposes ambiguity or an extra join path with no benefit. Since the existing Sales→Product relationship already provides the needed filter propagation, adding a category table only complicates the model and offers zero performance improvement.
- ✓
Create a relationship between Sales and Product on ProductID, and use Product[Category] in the measure.
Why this is correct
Creating a relationship from Sales to Product on ProductID and using Product[Category] inside the measure is the correct star-schema design. When the measure evaluates, category filters from the Product table propagate down the one-to-many relationship to Sales, and PowerPoint BI's in-memory engine pushes that filtering efficiently to the compressed fact table. This preserves the 50-million-row fact table at its lowest grain while allowing any product attribute to be used in measures without extra storage or materialization, making it ideal for performance and maintainability.
Go deeper
Related to this question
About these practice questions
This PL-300 question is part of Courseiva's 524-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.