The resultset of a query to external data source has exceeded the maximum allowed size of '1000000'

[SirishaD]

"I’m using Direct Query mode for my report, and when I try to display the top 10 results, I encounter the error: 'The resultset of a query to external data source has exceeded the maximum allowed size of 1,000,000 rows.' We're unable to reduce the data at the source. Is there any possible solution to this issue? I'm using SUM(column name) in my measure to display the top 10. The data loads fine when I apply a slicer filter, but when no slicer is selected, I get the error."

[Ritaf1983 · Accepted solution]

Hi @SirishaD   This "error" is a known issue due to the limitations of DirectQuery mode in Power BI. DirectQuery has a maximum result set size of 1,000,000 rows to prevent performance issues and reduce the load on the underlying data source. When your query exceeds this limit, Power BI returns the error you're encountering.

Here are several ways to address this issue:

Use Aggregation Tables:

Create pre-aggregated tables to reduce the number of rows queried by Power BI, minimizing the data size and preventing the error.
Implement RankX with Top N Filtering:

Use a DAX measure to rank and display only the top 10 results within Power BI. This limits the rows returned without relying on the data source to handle the sorting:
DAX
Copy code
Top10Measure = IF ( RANKX(ALL('YourTable'), [SUM(column name]), , DESC) <= 10, [SUM(column name]), BLANK() )
Apply Filters at the Report Level:

Enforce default slicer selections or report-level filters to ensure the dataset is always filtered to a manageable size (e.g., specific date ranges or categories).
Adjust DirectQuery Limitations in Power BI Desktop:

If query folding is supported, you can push down filters to the data source to limit the data size before it reaches Power BI. This can be achieved through SQL filters or database views.
Keep Top N Rows in Power Query:

In Power Query, use the "Keep Top Rows" function to limit the number of rows returned from the data source:
Go to Transform Data > Keep Rows > Keep Top Rows, and specify the number of rows to load.
This reduces the size of the dataset before it enters Power BI, helping to stay within the row limit.

If my answer was helpful please give me a Kudos and accept as a Solution.