PBI Service Visuals 10000 row limit

[iceman8766]

Hi all,

I'm facing the following issue: I have a ready report in PBI Desktop, sourcing from ODBC sources - all working correctly with no errors. When I publish the report on the PBI Service however, some of the visuals give the following error:

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

Has anyone found a solution to this case?

Thanks.

[iceman8766]

@v-tangjie-msft Thanks a lot for the detailed description. I ended up switching to import mode, yes it means spending slightly more time refreshing, but it will be worth it in the long-run, since I got a more stable connection to the database.

Cheers!

[v-tangjie-msft · Accepted solution]

Hi @iceman8766 ,

One of the query limits that you might encounter is the maximum allowed size of the result set, which is the number of rows that Power BI can retrieve from the data source for each query. By default, this limit is set to 1 million rows for models created by Power BI Desktop, but it can be lower for models published to Power BI Service, depending on the data source type and configuration.

Here are some suggestions that you can try:

- Reduce the number of rows returned by your query by applying filters or aggregations in Power BI or in the ODBC source.

- Switch from DirectQuery mode to Import mode or Composite mode for your data connection. Import mode means that Power BI imports and caches the data from the ODBC source, which can improve performance and avoid query limits, but it also requires refreshing the data periodically and may encounter data size limitations. Composite mode means that Power BI uses a combination of DirectQuery and Import modes for different tables in your model, which can offer more flexibility and balance between performance and freshness.

Please refer to the following documents for more information.

Tuning query limits for DirectQuery - SQLBI

Power BI DirectQuery Mode And Degenerate Dimensions - Chris Webb's BI Blog (crossjoin.co.uk)

Best Regards,

Neeko Tang

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