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

[SBC]

Hi ,

Iam Experiencing the error coludnt load the data  for this visual

I have only one table with four columns in the source with huge datacontains mor than 1millioin rows,I have pulled the data into  power bi by using direct query by applying  two filters    as well but still iam facing the error of "The resultset of a query to external data source has exceeded the maximum allowed size of ‘1000000’".

please let me know how to resolve this issue other than applying filters in the report.

Thank You.

[Anonymous · Accepted solution]

Hi @SBC ,

This is a known issue that appears on directquery mode datasource when it return rows more than 1M.

Use DirectQuery in Power BI Desktop#limitations-of-directquery 

This is caused by 'return' rows, have you apply any t-sql queries or functions that will increase the return row amount? (e.g. join functions)

In addition,if  you are nested multiple iterator functions in your measure which will multiple its row contents amount.
I'd like to suggest you try to remove them from your visual to confirm if this issue is caused by these multiplied row contents.
Notice: iterate tables will obviously increase the calculation table row contents amount.
for example, iterator three tables with 1k, 2k, 5k rows.
Actual calculated row contents= 1000*2000*5000=10^10 rows that obviously over 1M(10^6) rows.

Optimizing nested iterators in DAX 

I have also found a similar post, please refer to it to see if it helps you.

Error: Resultset of a query to external data source exceeded the maximum allowed size of 100000 rows 

•  create a filter measure 
• put measure Filter1 in the Visual Level Filter of goal visual
• set the Filter1 as "is not blank"

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@SBC , Is it a direct query. In direct query-based data should not return more than 1 M rows . Please check the measure used and try to rewrite two queries with less data, Or you define aggregate tables 

https://radacad.com/power-bi-aggregation-step-1-create-the-aggregated-table