Directquery Row limit

[Anonymous]

I have a table having more than 50 lacs of data and is in directquery mode. There are Slicers and matrix visuals on the report page. while loading the matrix visual I am getting error of "the resultset of a query to external data source has exceeded the maximum allowed size of 1000000 rows.

The visual is summarising count based on 2 tables, one of which is having these 50 Lacs of record.. If I run a SQL query to summaerise these columns in SQLDB enviornment, it runs perfectly and the output does not contain records more than 100. when I checked SQL query generated in Power BI Desktop using performane analyser, it has a select clause as "SELECT
TOP (1000001)" 

Can someone help me in understanding  exactly how this works

[amitchandak]

@Anonymous , Do you have bi-directional join.

refer this

https://www.youtube.com/watch?v=4kVw0eaz5Ws

[Anonymous]

There is one to Many relationship between the table with cross filtering direction set as Single. The error generally occurs when slicers have All. If any specific value is selected then it works as expected. 

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

This blog will help you understand why this error occurs and how to increase the row limit to adapt your scenario.

https://www.sqlbi.com/articles/tuning-query-limits-for-directquery/

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.