Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
iceman8766
New Member

PBI Service Visuals 10000 row limit

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.

1 ACCEPTED SOLUTION
v-tangjie-msft
Community Support
Community Support

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. 

View solution in original post

2 REPLIES 2
iceman8766
New Member

@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
Community Support
Community Support

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. 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.