If you’re preparing for the PL-300: Microsoft Power BI Data Analyst certification, understanding relationships between tables in your data model is crucial. Let’s walk through a common question that appears in practice assessments and explore not just the correct answer, but why it’s the best choice.
📌 Question:
Your organization has a Power BI model with multiple tables, including a ‘Sales’ table with transactional data and a ‘Products’ table with product details.
You need to aggregate sales data by product categories.
What should you do? (Select only one answer)
- Add a calculated column in the ‘Sales’ table for the ‘Category’ column.
- Create a many-to-one relationship from ‘Sales’ to ‘Products’.
- Define a many-to-many relationship between the ‘Sales’ and ‘Products’ tables.
- Use the ‘Autodetect’ feature to create relationships.
- Create a one-to-many relationship from ‘Sales’ to ‘Products’.
✅ Correct Answer: 2. Create a many-to-one relationship from ‘Sales’ to ‘Products’.
🧠 Why This is the Right Answer:
In Power BI, when working with related tables—especially when performing aggregations like summing sales by category—you need to establish proper relationships between your tables.
Let’s break it down:
🗃️ Table Structure:
- ‘Sales’ Table: Contains many rows, each representing a transaction involving a product.
- ‘Products’ Table: Contains one row per product, including its Category (e.g., Electronics, Apparel, etc.)
This setup creates a classic many-to-one relationship, where:
- Many Sales entries can refer to
- One Product in the Products table.
Thus, the correct relationship is:
👉 ‘Sales'[ProductID] (many) → ‘Products'[ProductID] (one)
Once this relationship is established, you can easily aggregate sales by any attribute in the Products table, including Category.
🚫 Why the Other Options are Incorrect:
1. Add a calculated column in the ‘Sales’ table for the ‘Category’ column.
- This creates redundancy.
- It breaks normalization principles and is inefficient for large datasets.
- Relationships exist to avoid this kind of duplication.
3. Define a many-to-many relationship between ‘Sales’ and ‘Products’.
- Not applicable here.
- Every transaction in ‘Sales’ links to a single product; there is no many-to-many scenario.
4. Use the ‘Autodetect’ feature to create relationships.
- While Autodetect may help, it’s not a guaranteed solution.
- It’s not the best answer when you are explicitly asked to choose the right modeling step.
5. Create a one-to-many relationship from ‘Sales’ to ‘Products’.
- This is incorrect because each product can have many sales, not the other way around.
- The direction should be from Sales (many) to Products (one).
🏁 Final Thoughts:
For this question, knowing your data modeling basics is key. Always ensure your relationships reflect real-world cardinality. The many-to-one relationship from Sales to Products allows you to perform accurate and efficient aggregations, such as total sales by category.
Understanding and mastering relationships in Power BI isn’t just essential for the PL-300 exam—it’s critical for building robust and scalable data models in the real world. ✅
👉 Ready for more PL-300 practice questions?
Subscribe to our newsletter for more real-world Power BI scenarios explained in simple terms!