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

[waqas]

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

[ManuGP]

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?

[nmckeown1]

How do you filter it before joining the tables?

[jburchel]

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]

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/