Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
waqas
New Member

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

I am using direct query from SQL server database running locally in the machine. Getting this error:

 

Error Message:

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

 

How I can overcome this limit, is there's a way?

 

-Waqas

4 REPLIES 4
ManuGP
Helper I
Helper I

I am getting this message in a situation where there is NO WAY the query resultset exceeds 1mill rows (in fact it should return only one row with the filter I'm putting). It is a simple model with two tables in 1:* relationship, for the query I'm filtering the * side table and the 1 side table contains more than 1000000 records. So in my opinion the message is misleading and the problem is really when AFTER applying filters in each table and before the join between the tables each of the tables involved need to be restricted to under 1000000 records. So my result should be one row, but as the join needs to go through an unrestricted table of more than a million records, I get the error message: "The resultset of a query to external data source has exceeded the maximum allowed size of '1000000'"

A better wording (for this case at least) might be: "The resultset of one of the queries to external data sources has exceeded the maximum allowed size of '1000000' "

 

This make sense BUT the weird thing is: If I select (only) fields from one of the tables including the key, without applying any filter at all, then the result is a list of more than a million rows and there is no error message even though I'm exceeding the limit. So I guess the limit is only when querying a join to external tables.

 

Is my analysis is correct? What do you think?

How do you filter it before joining the tables?

jburchel
Microsoft Employee
Microsoft Employee

This limit is actually in the underlying Analysis Services tabular database, a default limitation on direct query sources for performance reasons.  The behavior is part of the current design of the product, but you can request the product group consider raising it, since it could internally be reconfigured.  You can submit direct feedback or search existing feedback (about this topic though, I found  none yet), at https://powerbi.microsoft.com/en-us/blog/tag/product-feedback/.  The feedback with the most upvotes is prioritized, and the product group actively monitors this forum to target most in-demand features.  

 

It may be this was simply the default from underlying Analysis Services configuration, and the Power BI team might be willing to consider raising it then, if there are no negative repurcusions to consider.  Performance cost to the service of large rowsets should not be impactful since normal data limits would still apply, so the speed of execution could be determined acceptable or not by the report designer at design time when considering the refresh speed of directquery rowsets larger than the default 1,000,000 row limit.  For the purposes of the service then, it may not be so impactful a change for the product group to consider.  In other cases, like with memory limits in the cloud, they are imposed for the general health of the service, but this one might be simpler to change without having repercussions outside the report.

Greg_Deckler
Super User
Super User

That might be a hard limit, it seems conspicuously round and corresponds to the limit identified for Streaming data sources:

 

Consume streaming data in your dashboards and reports

 

from https://powerbi.microsoft.com/en-us/pricing/



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.