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.

Practice

1. What is the main benefit of using multiple data sources in one Power BI report?

easy

A. You can combine different types of data for richer insights.

B. It automatically cleans all data without user input.

C. It limits the report to only one data table.

D. It removes the need for relationships between tables.

Solution

1. Step 1: Understand the purpose of multiple data sources

   Multiple data sources allow combining different datasets to get a fuller picture.

2. Step 2: Identify the benefit in report insights

   Combining data from various sources enriches the analysis and insights.

3. Final Answer:

   You can combine different types of data for richer insights. -> Option A

4. Quick Check:

   Multiple data sources = richer insights [OK]

Hint: Multiple sources mean combining data for better insights [OK]

Common Mistakes:

• Thinking multiple sources clean data automatically
• Believing multiple sources limit data to one table
• Assuming relationships are not needed

2. Which of the following is the correct way to add a new data source in Power BI Desktop?

easy

A. Right-click on a visual and select Add Data Source

B. Use the Modeling tab to create a new data source

C. Go to Home tab > Get Data > Choose source > Load

D. Drag and drop files directly onto the report canvas

Solution

1. Step 1: Recall the process to add data

   In Power BI Desktop, new data sources are added via Home tab's Get Data option.

2. Step 2: Confirm the correct menu and steps

   Choose the source type and then load the data into the report.

3. Final Answer:

   Go to Home tab > Get Data > Choose source > Load -> Option C

4. Quick Check:

   Get Data on Home tab = add source [OK]

Hint: Use Home > Get Data to add sources [OK]

Common Mistakes:

• Trying to add data from the Modeling tab
• Right-clicking visuals to add data
• Dragging files onto the canvas doesn't load data

3. Given two tables from different data sources: Sales and Products, which DAX formula correctly calculates total sales amount by product category assuming a relationship exists?

medium

A. Total Sales = SUM(Sales[Amount])

B. Total Sales = CALCULATE(SUM(Sales[Amount]), Products[Category])

C. Total Sales = SUMX(Products, RELATED(Sales[Amount]))

D. Total Sales = SUMX(Products, SUM(Sales[Amount]))

Solution

1. Step 1: Understand the relationship usage

   With a relationship between Sales and Products, SUM(Sales[Amount]) aggregates sales correctly by category in visuals.

2. Step 2: Evaluate each formula

   Total Sales = SUM(Sales[Amount]) sums Sales amount directly, which works with relationships. Total Sales = CALCULATE(SUM(Sales[Amount]), Products[Category]) syntax is invalid. Total Sales = SUMX(Products, RELATED(Sales[Amount])) misuses RELATED. Total Sales = SUMX(Products, SUM(Sales[Amount])) incorrectly sums the grand total for each product row, inflating results.

3. Final Answer:

   Total Sales = SUM(Sales[Amount]) -> Option A

4. Quick Check:

   SUM with relationships = correct total [OK]

Hint: Use SUM on sales column when relationships exist [OK]

Common Mistakes:

• Using CALCULATE without filter expression
• Misusing RELATED in SUMX
• Overcomplicating simple SUM aggregation

4. You imported two data sources but your report shows no combined data. What is the most likely issue?

medium

A. You added too many visuals on the report page.

B. You did not refresh the data after import.

C. You used the wrong file format for import.

D. You forgot to create relationships between tables.

Solution

1. Step 1: Identify why combined data is missing

   Without relationships, tables from different sources cannot connect, so combined data won't show.

2. Step 2: Check other options

   Refreshing data or file format issues usually cause errors, not missing combined data. Too many visuals do not affect data combination.

3. Final Answer:

   You forgot to create relationships between tables. -> Option D

4. Quick Check:

   Missing relationships = no combined data [OK]

Hint: Create relationships to combine data sources [OK]

Common Mistakes:

• Ignoring relationships after importing data
• Assuming refresh fixes missing relationships
• Blaming visuals for data connection issues

5. You have sales data from Excel and customer data from SQL Server. You want to create a report showing total sales per customer region. How should you combine these sources effectively?

hard

A. Create separate reports for each source and link them with bookmarks.

B. Load both sources, create a relationship between customer IDs, then build visuals using fields from both tables.

C. Load only the sales data and manually type customer regions in Power BI.

D. Merge the Excel and SQL tables into one before loading into Power BI.

Solution

1. Step 1: Load both data sources separately

   Import Excel sales data and SQL customer data into Power BI as separate tables.

2. Step 2: Create a relationship between customer IDs

   Link the tables on customer ID to connect sales with customer regions.

3. Step 3: Build visuals using fields from both tables

   Use the relationship to show total sales by customer region in one report.

4. Final Answer:

   Load both sources, create a relationship between customer IDs, then build visuals using fields from both tables. -> Option B

5. Quick Check:

   Separate sources + relationship + visuals = combined report [OK]

Hint: Load sources, relate keys, then build combined visuals [OK]

Common Mistakes:

• Trying to merge tables outside Power BI unnecessarily
• Ignoring relationships and typing data manually
• Splitting data into separate reports instead of combining