Multiple data sources in one report in Power BI - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Why use multiple data sources in one Power BI report?

Imagine you want to create a sales report that combines data from your online store and your physical store. Why would you use multiple data sources in one Power BI report?

A Because Power BI cannot handle large datasets from a single source.

B To combine different types of data that come from separate systems into one report for better insights.

C To make the report load faster by splitting data into many sources.

D To avoid using relationships between tables in the data model.

💡 Hint

Think about why combining data from different places helps you see the full picture.

Using multiple data sources lets you bring together data from different systems, like online and physical stores, so you can analyze all sales in one place.

❓ dax_lod_result

intermediate

DAX measure combining data from two sources

You have two tables: OnlineSales and StoreSales. Both have a column Amount. You want a measure that sums sales from both sources. Which DAX measure gives the correct total sales?

A Total Sales = SUM(OnlineSales[Amount]) + SUM(StoreSales[Amount])

B Total Sales = SUM(OnlineSales[Amount] + StoreSales[Amount])

C Total Sales = CALCULATE(SUM(OnlineSales[Amount]), StoreSales[Amount])

D Total Sales = SUMX(OnlineSales, OnlineSales[Amount]) + SUMX(StoreSales, StoreSales[Amount])

💡 Hint

Think about how to add sums from two separate tables.

Option A correctly sums each table's Amount column separately and adds the results. Option A tries to sum columns from different tables inside SUM, which is invalid. Option A misuses CALCULATE. Option A uses SUMX but misses table references properly.

❓ visualization

advanced

Best visualization to compare sales from two sources

You want to show monthly sales from OnlineSales and StoreSales side by side for easy comparison. Which visualization type is best?

A Pie chart showing total sales from both sources combined.

B Line chart showing cumulative sales over the year from both sources combined.

C Clustered column chart showing separate columns for OnlineSales and StoreSales per month.

D Stacked column chart showing total sales per month combining both sources stacked.

💡 Hint

Think about how to compare two categories side by side for each month.

A clustered column chart shows separate bars for each sales source side by side per month, making comparison easy. Stacked columns combine values, hiding individual source amounts. Pie charts do not show time trends. Line charts combine sources, not showing separate values clearly.

❓ data_modeling

advanced

Handling relationships with multiple data sources

You have two data sources: OnlineSales and StoreSales. Both have a ProductID column. You also have a Products table. How should you model relationships to analyze sales by product?

A Do not create relationships; use LOOKUPVALUE in measures instead.

B Create many-to-many relationships between Products and both sales tables on ProductID.

C Create one-to-one relationships between Products and each sales table on ProductID.

D Create one-to-many relationships from Products to OnlineSales and Products to StoreSales on ProductID.

💡 Hint

Think about how product details relate to many sales records.

Products is a lookup table with unique ProductID values. Sales tables have many rows per product. So, one-to-many relationships from Products to each sales table on ProductID are correct. Many-to-many or one-to-one are incorrect here. Using LOOKUPVALUE is less efficient and unnecessary with proper relationships.

🔧 Formula Fix

expert

Why does this combined sales measure show incorrect totals?

You wrote this DAX measure to sum sales from two sources:

Total Sales = SUM(OnlineSales[Amount]) + SUM(StoreSales[Amount])

But the total is double what you expect when filtering by product. What is the likely cause?

A There is a many-to-many relationship causing double counting between sales tables and Products.

B The Amount columns contain text values causing incorrect sums.

C The measure syntax is wrong; SUM cannot be added like this.

D The report is missing a filter on the date column.

💡 Hint

Think about how relationships affect filtering and totals.

If both sales tables relate to Products with many-to-many or ambiguous relationships, filtering by product can cause double counting. The measure syntax is correct. Text values would cause errors, not double totals. Missing date filters would not cause doubling.