PL-300 Model the data Practice Question
You are designing a Power BI semantic model for an e-commerce company. You have a fact table with OrderID, CustomerID, OrderDate, and SalesAmount. You also have a Customers table with CustomerID, CustomerName, and CustomerSegment. You need to ensure that filters on CustomerSegment propagate to the SalesAmount measure. What should you do?
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 one-to-many relationship from Customers[CustomerID] to Sales[CustomerID].
Creating a one-to-many relationship from Customers[CustomerID] to Sales[CustomerID] establishes the Customers table as the lookup (dimension) table and Sales as the related (fact) table, so filter context applied to Customers[CustomerSegment] flows down to the Sales table and affects the SalesAmount measure. This is the standard star-schema filter propagation behavior in Power BI. Option A does not propagate filters; LOOKUPVALUE merely retrieves a value row-by-row in a calculated column and cannot drive cross-table filtering. Option C would work functionally but destroys the dimensional model and is not the recommended design. Option D adds a Segment table but does not by itself create the needed relationship from Customers to Sales, so segment filters would not reach SalesAmount.
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 LOOKUPVALUE function in a calculated column.
Why it's wrong here
Using LOOKUPVALUE in a calculated column forces Power BI to evaluate the lookup row-by-row for every row in the Sales table, creating a hidden, inefficient table scan that runs at refresh time and increases model size. This approach bypasses the in-memory relationship engine, so filter context from Customers will not propagate to Sales automatically. It also prevents the storage engine from leveraging columnar compression and pre-aggregations, making queries slower and less maintainable than a proper star schema.
- ✓
Create a one-to-many relationship from Customers[CustomerID] to Sales[CustomerID].
Why this is correct
Creating a one-to-many relationship from Customers[CustomerID] to Sales[CustomerID] is the correct star schema design. This relationship lets filter context flow from the dimension table (Customers) to the fact table (Sales), enabling correct aggregations like total sales by customer name. It also uses Power BI's optimized VertiPaq engine to join tables only when needed, reducing memory and improving query performance compared with calculated columns or merged tables. The cardinality is correctly specified because one customer can appear in many sales records.
- ✗
Merge the Customers and Sales tables into a single table in Power Query.
Why it's wrong here
Merging Customers and Sales into a single table in Power Query creates a flat, denormalized table that duplicates customer attributes (name, region, segment) on every sales row. This bloats the model, increases storage and memory usage, and degrades refresh and query performance because every aggregation must scan duplicated values. It also destroys the star schema separation of concerns, making it harder to reuse dimensions or modify business logic without reprocessing the entire table. Unlike a relationship, a merged table cannot independently aggregate customer-level measures or preserve grain differences.
- ✗
Create a snowflake schema by adding a separate Segment table.
Why it's wrong here
Creating a snowflake schema by adding a separate Segment table normalizes the customer dimension instead of keeping it as a single, flattened star-schema dimension. While this reduces redundancy in relational databases, in Power BI it adds extra joins and requires multiple relationships to resolve filters, which increases query complexity and slows performance. Power BI's VertiPaq engine performs best with denormalized star schemas, so a snowflake design is generally avoided. The Segment information should instead be included as a column within the Customers dimension to keep the model simple and efficient.
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.