error: maximum allowed size of 100000 rows on SQL Dataset

[nmckeown1]

I have a dataset that was created from SQL Database, and it has been refreshing perfectly on power BI service. Then today the refresh failed with the error: 

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

When opening the dataset on desktop, the refresh populates the same error for every single table in the model. Some tables are import mode and the rest are dual mode stroage, so i dont think the storage mode is the issue. 

The visuals all still populate so I cannot see where exactly the issue may be - or if it is with every table.

Anybody any idea how to solve this?

Is the issue with SQL rather than dax/power query?

[rajendraongole1]

Hi @nmckeown1 -The issue might be related to how Power BI interacts with the SQL database rather than the SQL database itself. The error message "Resultset of a query to external data source exceeded the maximum allowed size of 100000 rows" suggests a limitation in the data retrieval process.

you can try to reduce the number of records extraction in power query by filters to reduce the number of rows, such as by date range or other criteria or 

you can try with extraction of data from Direct query mode.

Hope it helps

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

[nmckeown1]

I'm not sure what filters I could add as I require all rows of data for the past lot of years. I already created an aggregated table for the fact table which only pulls the required columns, and all of my measures are created from the aggregated table. 

My fact table is in direct query mode. Should I change any of these properties?