Why data modeling connects related tables in Power BI - Challenge Your Understanding

Challenge - 5 Problems

🧠 Conceptual

intermediate

Why do we connect related tables in data modeling?

In Power BI, why is it important to create relationships between related tables?

A To enable filtering and aggregation across tables for accurate reports

B To make tables load faster in Power BI Desktop

C To prevent users from editing data in the tables

D To reduce the file size of the Power BI report

💡 Hint

Think about how data from different tables can work together in reports.

Connecting related tables allows Power BI to filter and summarize data correctly across tables, enabling accurate and meaningful reports.

❓ dax_lod_result

intermediate

DAX measure with related table filter

Given two tables: Sales and Products connected by ProductID, what is the result of this DAX measure?

SalesAmountFiltered = CALCULATE(SUM(Sales[Amount]), Products[Category] = "Beverages")

If Sales table has amounts: 100, 200, 300 for products in categories Beverages, Snacks, Beverages respectively, what is the measure value?

A 600

B 400

C 300

D 100

💡 Hint

Only sum amounts where product category is Beverages.

The measure sums Sales amounts only for products in the Beverages category, which are 100 and 300, totaling 400.

❓ visualization

advanced

Choose the correct visualization for related tables

You have two related tables: Customers and Orders. You want to show total orders per customer with a clear connection between them. Which visualization best shows this?

A A line chart showing order amounts over time without customer info

B A pie chart showing total orders by order date

C A table showing customers without order totals

D A bar chart showing total orders by customer name

💡 Hint

Think about showing totals grouped by customer.

A bar chart grouped by customer name clearly shows total orders per customer, leveraging the relationship between Customers and Orders tables.

🔧 Formula Fix

advanced

Why does this report show incorrect totals?

You created a report summing sales amounts, but totals are too high. You have two tables: Sales and Customers. You forgot to create a relationship between them. What is the likely cause?

A Without a relationship, Power BI sums all sales repeatedly for each customer, inflating totals

B Power BI automatically creates relationships, so this should not happen

C The sales amounts are stored as text, causing wrong sums

D The report filter is set to exclude customers, causing totals to be low

💡 Hint

Think about how missing relationships affect data aggregation.

Without a relationship, Power BI cannot match sales to customers properly, causing duplicated sums and inflated totals.

🎯 Scenario

expert

Designing a data model for sales and products

You have two tables: Sales with columns (SaleID, ProductID, Quantity, Amount) and Products with (ProductID, ProductName, Category). You want to analyze total sales by product category. What is the best data modeling approach?

A Merge both tables into one to avoid relationships

B Create a many-to-many relationship between Sales and Products on ProductID

C Create a one-to-many relationship from Products (one) to Sales (many) on ProductID

D Create a relationship from Sales (one) to Products (many) on ProductID

💡 Hint

Think about which table has unique products and which has multiple sales per product.

Products table has unique ProductID values (one side), and Sales has many rows per product (many side). A one-to-many relationship from Products to Sales on ProductID is best.