Power BI DirectQuery Performance Features

Answer Correct answer: A, B — Use user-defined aggregations and automatic aggregation to reduce query execution time by minimizing the data scanned from the source.

You have a Fabric workspace that contains a DirectQuery semantic model. The model queries a data source that has 500 million rows. You have a Microsoft Power Bi report named Report1 that uses the model. Report1 contains visuals on multiple pages. You need to reduce the query execution time for the visuals on all the pages. What are two features that you can use? Each correct answer presents a complete solution, NOTE: Each correct answer is worth one point.

user-defined aggregations Correct Answer
automatic aggregation Correct Answer
query caching
OneLake integration

power bi directquery optimization user defined aggregations power bi automatic aggregations power bi dp-600 exam questions

Community Votes

100%

100% of anonymous learners picked answer AB. Votes are pick records left by other test-takers — they are not the verified answer.

Community Insight

The exam tests knowledge of specific performance optimization tools for DirectQuery models, specifically distinguishing between user-defined and automatic aggregations versus caching or integration features.

This question addresses reducing query execution time for large DirectQuery datasets in Power BI. The correct solution involves using aggregation features to minimize data scanning.

Many candidates incorrectly select Query Caching (C), believing it is a primary feature for all visuals. However, caching is often limited to the first page load or specific scenarios, whereas aggregations actively reduce the data volume scanned by the engine for all queries.

Community Discussion (13 comments)

sraakesh95 👍 19 Selected: AB

Agree with lengzhai's reference of the 2 links: A - Custom aggregations enables PBI to not perform a Full Scan of the underlying datasets. B - The AutoAggregations feature automatically creates aggregations on large datasets and based on query optimization determines the total number of rows that requires processing based on the generated query plan. Incorrect to this question context: C - Although caching helps improve performance on large datasets, it doesn't support DirectQuery (Important note in https://learn.microsoft.com/en-us/power-bi/connect-data/power-bi-query-caching) ; Also, it is a feature available in PBI Service that is automatic and needs no intervention from the user.

Momoanwar 👍 11 Selected: AB

D: onelake integration not for Direct Query C: only at loading for first page So AV

NRezgui 👍 1 Selected: AB

A. user-defined aggregations B. automatic aggregation

AshwiniVivek 👍 2 Selected: AC

User-defined aggregations (A): User-defined aggregations allow you to create pre-aggregated tables that summarize data at a higher level, reducing the volume of data that needs to be queried for specific visuals. This can significantly improve performance for common queries by avoiding the need to scan the entire dataset. Query caching (C): Query caching stores the results of previous queries for reuse. When users interact with visuals that require similar data, the cached results can be returned more quickly than querying the source again, thereby reducing execution time for those visuals.

Rakesh16 👍 2 Selected: AB

A & B is the answer

6d1de25 👍 1 Selected: BD

B&D are correct. Direct Lakes are great for performance in the OneLake integration https://learn.microsoft.com/en-us/fabric/get-started/direct-lake-overview

282b85d 👍 1

A&B While query caching can be beneficial in certain scenarios, user-defined aggregations and automatic aggregations are typically more effective for improving query performance in Power BI reports with large datasets and complex queries. These methods reduce the volume of data processed in real-time queries, directly addressing the performance bottlenecks associated with querying large datasets.

Murtaza_007 👍 1

CHATGPT saya AC

stilferx 👍 1 Selected: AB

IMHO, A & B looks good

lengzhai 👍 5 Selected: AB

Agree with A B https://learn.microsoft.com/en-us/power-bi/transform-model/aggregations-advanced https://learn.microsoft.com/en-us/power-bi/enterprise/aggregations-auto

estrelle2008 👍 2 Selected: AB

Agreed AB. Although query caching (C) will reduce query execution time too, you risk outdated cached results when working with real-time or dynamic data.

Fermd 👍 4 Selected: AC

A. User-defined aggregations (UDAs) allow you to pre-aggregate specific calculations directly in the semantic model. This reduces the amount of data that needs to be retrieved from the source each time a visual requires the calculation, significantly improving query execution time. C. Power BI Desktop enables query caching for DirectQuery models. This stores frequently used queries on the client machine, eliminating the need to re-send them to the source data for subsequent interactions.

Nicofr 👍 2 Selected: BD

https://learn.microsoft.com/en-us/power-bi/enterprise/aggregations-auto https://learn.microsoft.com/en-us/power-bi/enterprise/onelake-integration-overview

Comments & Corrections

No comments yet — spotted an error or have a note? Share it below.

Expert Analysis

Why the Answer Is Correct

Both User-defined aggregations (A) and Automatic aggregations (B) are designed to optimize DirectQuery performance by allowing the engine to scan smaller summary tables instead of the full 500 million row dataset. This directly reduces query execution time for visuals across all pages.

Why the Other Options Are Wrong

Query caching (C) primarily caches results for the initial page load and does not reliably accelerate subsequent interactions or new visual queries on other pages as effectively as aggregations. OneLake integration (D) refers to storage architecture (DirectLake mode) rather than a feature you 'use' to optimize an existing DirectQuery model's execution time in this context.

Community Comment Notes

Community consensus strongly favors AB, with users noting that aggregations prevent full scans. Some users argued for C, but the majority clarified that caching is insufficient for the broad scope of 'visuals on multiple pages' compared to the structural optimization provided by aggregations.

Official Reference

Exam Strategy

When asked about optimizing DirectQuery performance for large datasets, prioritize 'Aggregations' (both user-defined and automatic). Avoid 'Caching' unless the question specifically mentions repeated identical queries on the same page, as aggregations are the robust architectural solution for reducing scan volume.

Frequently Asked Questions

Why is Query Caching not the best answer?

Caching stores results for the first page load but does not consistently optimize queries for new visuals or pages as effectively as aggregations which reduce the underlying data volume.

What is the difference between User-defined and Automatic Aggregations?

User-defined aggregations require manual creation of summary tables, while Automatic aggregations are created by the service based on query patterns without manual intervention.

Related Analysis

Practice All DP-600 Questions

Access 115 questions with complete answers and detailed explanations.

View Full DP-600 Practice Test →

← Back to DP-600 Study Guide