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.