PL-300 Model the data Practice Question
You are designing a data model for a financial analysis report. The source data includes a 'Budget' table with columns: Department, Account, Month, and BudgetAmount. The 'Actuals' table has the same structure. You need to create a combined measure that shows the variance (Actual - Budget) for each Department and Account. What is the best approach?
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 separate dimension tables for Department and Account, and create fact tables for Budget and Actuals with relationships
Option A is correct because a star schema with shared Department and Account dimensions and separate Budget and Actuals fact tables lets each fact table relate to the same dimensions, so a DAX measure such as [Actual] - [Budget] can compute variance correctly at every Department/Account granularity and slice consistently. Keeping Budget and Actuals as distinct fact tables also preserves their different business meanings and avoids double-counting or ambiguous filter propagation. Option B is wrong because merging the two tables into one row-level table destroys the separate fact semantics and can misalign or duplicate rows when granularity differs. Option C is wrong because a calculated SUMMARIZE table materializes data and is not the recommended way to model two fact sources for flexible variance analysis. Option D is wrong because appending Budget and Actuals with a Type column creates a single fact table whose measures must filter by Type, which is less clean and can produce incorrect aggregation across the combined rows.
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 separate dimension tables for Department and Account, and create fact tables for Budget and Actuals with relationships
Why this is correct
This star schema design is optimal because Department and Account become conformed dimensions that can filter both fact tables without duplicating attributes. Budget and Actuals remain separate fact tables, enabling measures like SUM(Actuals[Amount]) and SUM(Budget[Amount]) to be compared directly using DAX, with relationships enforcing correct row context. This setup preserves referential integrity, supports drill-through, and lets the engine optimize query performance by navigating relationships rather than scanning stacked data.
- ✗
Create a single table by merging Budget and Actuals on Department, Account, and Month
Why it's wrong here
Merging Budget and Actuals into a single table on Department, Account, and Month creates a denormalized, wide table where each row must hold both Budget and Actual amounts, often producing nulls when one side has no matching entry. This structure forces you to maintain a wide table with extra columns, makes it harder to add or modify fact-specific measures independently, and can lead to duplicate rows if any key is not unique. It also breaks star schema best practices, reducing filtering clarity and making future schema changes (like adding a new fact table) unnecessarily difficult.
- ✗
Create a calculated table using SUMMARIZE and then use DAX measures
Why it's wrong here
Using SUMMARIZE to build a calculated table is a poor modeling choice because the resulting table is static and does not automatically update when underlying data changes; you would need to recalculate or refresh manually, which is impractical in a live financial report. More importantly, it collapses fact-level granularity and creates a disconnected table that does not leverage the model's relationships, forcing you to write complex DAX with TREATAS or LOOKUPVALUE to filter it. This approach bypasses the relational engine's strengths and can lead to performance issues and inaccurate comparison measures.
- ✗
Use Power Query to append Budget and Actuals with a 'Type' column
Why it's wrong here
Appending Budget and Actuals in Power Query with a 'Type' column produces a long, stacked table that is not a proper star schema, because you now have a single fact table with a discriminator column instead of two independent fact tables. Every measure would require filtering by the Type value—e.g., CALCULATE(SUM([Amount]), [Type] = "Budget")—which is more verbose, less self-documenting, and often slower than querying separate fact tables with relationships. It also prevents the model from applying different aggregation logic or granularity to each flow, increasing DAX complexity and making user-facing measures error-prone when filters on Type are omitted.
Go deeper
Related to this question
About these practice questions
Courseiva writes every PL-300 question from scratch — 524 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.