PL-300 Prepare the data Practice Question
You are building a Power BI data model that combines Sales data from SQL Server and Marketing data from a CSV file. The Sales table has a unique 'OrderID' column, and the Marketing table has a 'CampaignID' column. You need to create a relationship between Sales and Marketing to analyze campaign effectiveness. What should you do?
⚠ Common exam trap
Candidates often assume an inactive relationship or a merge can handle mismatched keys, but Power BI requires a common key column for relationships, and merging destroys the normalized model needed for accurate many-to-many analysis.
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 bridge table containing unique combinations of OrderID and CampaignID.
A bridge table resolves the many-to-many relationship between Sales (OrderID) and Marketing (CampaignID). Since there is no direct key match, creating a bridge table with unique combinations of OrderID and CampaignID allows Power BI to model the relationship correctly and analyze campaign effectiveness without data duplication or ambiguity.
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 an inactive relationship between Sales and Marketing and activate it with USERELATIONSHIP in measures.
Why it's wrong here
An inactive relationship can only be activated when a table has unique keys on at least one side, enabling role-playing dimensions such as OrderDate versus ShipDate. In a many-to-many scenario, both Sales and Marketing contain duplicate OrderIDs or CampaignIDs, so even USERELATIONSHIP cannot disambiguate row matches without a bridge. It also only swaps the active relationship path; it does not prevent the fanout that occurs without a bridge table.
- ✓
Create a bridge table containing unique combinations of OrderID and CampaignID.
Why this is correct
A bridge table is a factless fact table that stores unique combinations of OrderID and CampaignID, representing marketing touchpoints for each order. Filtering the bridge from Marketing propagates OrderIDs to Sales, while filtering from Sales propagates CampaignIDs to Marketing, preserving one-to-many relationships in both directions. This eliminates fanout and ensures sales measures are not inflated when analyzing campaigns.
- ✗
Create a separate table for each campaign and relate to Sales.
Why it's wrong here
Creating a separate table for each campaign fragments the model and multiplies table counts, making maintenance and DAX logic unwieldy. It also breaks star-schema principles because campaigns are categorical attributes that should be modeled as rows, not tables. Cross-campaign comparisons would require dynamic UNIONs or multiple relationship paths, which is neither scalable nor performant.
- ✗
Merge the Marketing table into the Sales table using a left outer join.
Why it's wrong here
Merging Marketing into Sales with a left outer join inflates row counts because each sale row repeats once for every matching marketing record, causing sales totals to be overstated when aggregated. The two tables operate at different grains—sales may be per line item, marketing per touchpoint—so merging conflates levels of detail. It also destroys the ability to independently filter or slicer on marketing attributes.
Visual reference
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.