Aggregations for performance in Power BI - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ dax_lod_result

intermediate

Calculate Total Sales Using Aggregation Table

You have a large sales fact table and a smaller aggregation table that stores monthly total sales per product. Which DAX measure correctly retrieves the total sales for a selected month from the aggregation table to improve performance?

A Total Sales = CALCULATE(SUM('MonthlySalesAgg'[TotalSales]), FILTER('MonthlySalesAgg', 'MonthlySalesAgg'[Month] = SELECTEDVALUE('Sales'[Month])))

B Total Sales = SUM('Sales'[SalesAmount])

C Total Sales = SUM('MonthlySalesAgg'[TotalSales])

D Total Sales = CALCULATE(SUM('Sales'[SalesAmount]), ALLEXCEPT('Sales', 'Sales'[Month]))

💡 Hint

Think about which table is optimized for faster aggregation.

Using the aggregation table's pre-calculated totals improves performance by avoiding scanning the large fact table.

❓ visualization

intermediate

Best Visualization for Aggregated Data

You have aggregated sales data by region and month. Which visualization best helps users quickly compare total sales across regions over time?

A A line chart showing monthly sales trends for each region

B A clustered bar chart showing sales by region for a selected month

C A pie chart showing total sales by region for the entire year

D A table listing sales numbers by region and month

💡 Hint

Consider which chart type best shows changes over time for multiple categories.

A line chart clearly shows trends over time for each region, making it easier to compare performance.

❓ data_modeling

advanced

Designing Aggregation Tables for Performance

You want to improve report performance by creating aggregation tables. Which design principle is most important to ensure aggregations speed up queries?

A Include all columns from the fact table in the aggregation table to avoid joins

B Aggregate data at a higher granularity matching common query filters

C Create aggregation tables with calculated columns instead of measures

D Aggregate data at the lowest granularity possible to maximize detail

💡 Hint

Think about how users filter reports and what level of detail they need.

Aggregations should match common query patterns at a higher granularity to reduce data scanned and improve speed.

🔧 Formula Fix

advanced

Identify the Issue in Aggregation Measure

Given this DAX measure intended to use an aggregation table, what is the problem?

Aggregated Sales = CALCULATE(SUM('AggSales'[SalesAmount]), 'AggSales'[Year] = YEAR(TODAY()))

Power BI

Aggregated Sales = CALCULATE(SUM('AggSales'[SalesAmount]), 'AggSales'[Year] = YEAR(TODAY()))

A The measure causes a circular dependency error

B The measure will return sales for all years, ignoring the filter

C The filter condition syntax is invalid; it should use FILTER function

D The measure will return sales only for the current year correctly

💡 Hint

Check how filter conditions are written inside CALCULATE.

Direct column equals value inside CALCULATE needs to be wrapped in FILTER or use a proper filter expression.

🧠 Conceptual

expert

When to Use Aggregations vs Direct Query

In a Power BI model connected to a very large database, when is it better to use aggregation tables instead of relying on Direct Query mode?

A When the source database supports fast live connections without limits

B When the data model is small and fits entirely in memory

C When users need real-time data and cannot tolerate any delay

D When queries are slow and users mostly analyze summarized data

💡 Hint

Think about trade-offs between query speed and data freshness.

Aggregation tables speed up queries by pre-summarizing data, which helps when users mostly analyze summaries and performance is slow with Direct Query.

Practice

1.

What is the main benefit of using aggregations in Power BI reports?

easy

A. Speeding up report loading by summarizing detailed data

B. Increasing the size of the data model

C. Making data entry easier for users

D. Automatically fixing data errors

Solution

1. Step 1: Understand the purpose of aggregations

   Aggregations summarize detailed data into smaller, grouped summaries.

2. Step 2: Identify the effect on report performance

   Using these summaries reduces the amount of data Power BI processes, speeding up report loading.

3. Final Answer:

   Speeding up report loading by summarizing detailed data -> Option A

4. Quick Check:

   Aggregations improve speed [OK]

Hint: Aggregations reduce data detail to speed up reports [OK]

Common Mistakes:

• Thinking aggregations increase data size
• Confusing aggregations with data entry
• Assuming aggregations fix data errors

2.

Which of the following is the correct DAX syntax to create a simple aggregation measure that sums the SalesAmount column?

easy

A. Total Sales = AVERAGE(Sales[SalesAmount])

B. Total Sales = COUNT(Sales[SalesAmount])

C. Total Sales = SUM(Sales[SalesAmount])

D. Total Sales = SUMMARIZE(Sales, Sales[SalesAmount])

Solution

1. Step 1: Identify the aggregation function for summing

   The SUM function adds all values in a column.

2. Step 2: Check the syntax for summing SalesAmount

   Total Sales = SUM(Sales[SalesAmount]) correctly sums the column.

3. Final Answer:

   Total Sales = SUM(Sales[SalesAmount]) -> Option C

4. Quick Check:

   SUM function sums values [OK]

Hint: SUM() adds values; COUNT() counts rows [OK]

Common Mistakes:

• Using COUNT instead of SUM for totals
• Using SUMMARIZE incorrectly as aggregation
• Confusing AVERAGE with SUM

3.

Given a summary table SalesSummary with columns Region and TotalSales, what will the following DAX measure return?
Measure = CALCULATE(SUM(SalesSummary[TotalSales]), SalesSummary[Region] = "West")

medium

A. Sum of TotalSales for the West region only

B. Sum of TotalSales for all regions

C. Count of rows where region is West

D. Error due to incorrect filter syntax

Solution

1. Step 1: Understand the CALCULATE filter

   The filter SalesSummary[Region] = "West" limits data to West region.

2. Step 2: Calculate sum with filter applied

   SUM aggregates TotalSales only for rows where Region is West.

3. Final Answer:

   Sum of TotalSales for the West region only -> Option A

4. Quick Check:

   CALCULATE applies filter [OK]

Hint: CALCULATE applies filters to aggregation [OK]

Common Mistakes:

• Ignoring the filter and summing all regions
• Confusing SUM with COUNT
• Assuming syntax error in filter

4.

Identify the error in this DAX measure intended to sum SalesAmount for the East region:
Measure = CALCULATE(SUM(Sales[SalesAmount]), Sales[Region] == "East")

medium

A. SUM cannot be used inside CALCULATE

B. Using double equals (==) instead of single equals (=) in filter

C. Missing ALL() function to remove filters

D. SalesAmount column name is incorrect

Solution

1. Step 1: Check filter syntax inside CALCULATE

   DAX uses single equals (=) for filter conditions, not double equals (==).

2. Step 2: Confirm correct syntax for filter

   Replace Sales[Region] == "East" with Sales[Region] = "East" to fix error.

3. Final Answer:

   Using double equals (==) instead of single equals (=) in filter -> Option B

4. Quick Check:

   Use = for filters in DAX [OK]

Hint: Use single = for filters, not == [OK]

Common Mistakes:

• Using == like in other languages
• Thinking SUM can't be inside CALCULATE
• Adding unnecessary ALL() function

5.

You have a large sales dataset and want to improve report speed by creating an aggregation table that groups sales by Year and ProductCategory. Which approach is best to create this aggregation table in Power BI?

hard

A. Add a slicer for ProductCategory on the report page

B. Use FILTER(Sales, Sales[Year] = 2023) to reduce data

C. Create a calculated column with Sales[SalesAmount] * 2

D. Create a new table using SUMMARIZE(Sales, Sales[Year], Sales[ProductCategory], "TotalSales", SUM(Sales[SalesAmount]))

Solution

1. Step 1: Understand aggregation table creation

   Use SUMMARIZE to group data by Year and ProductCategory and calculate totals.

2. Step 2: Evaluate options for aggregation

   Create a new table using SUMMARIZE(Sales, Sales[Year], Sales[ProductCategory], "TotalSales", SUM(Sales[SalesAmount])) creates a summary table with grouped totals, ideal for aggregation.

3. Final Answer:

   Create a new table using SUMMARIZE(Sales, Sales[Year], Sales[ProductCategory], "TotalSales", SUM(Sales[SalesAmount])) -> Option D

4. Quick Check:

   SUMMARIZE creates aggregation tables [OK]

Hint: Use SUMMARIZE to group and sum for aggregation tables [OK]

Common Mistakes:

• Using FILTER instead of grouping
• Creating calculated columns instead of tables
• Relying on slicers for performance