Direct Query row limit in star schema

Hi, Anonymous 

Based on my research, there is a fixed limit of 1 million rows placed on the number of rows that can be returned in any single query to the underlying source. The limit can occur in cases where Power BI isn't fully optimizing the queries sent, and there's some intermediate result being requested that exceeds the limit even though the final result doesn't exceeds the limit.

Here are some suggestions for DircetQuery best practices.

DB design guidance

• Push calculated columns and measures to the source where possible. The closer to the source, the higher the likelihood of performance.
• Optimize! Understand the execution plans for your queries, add indices for commonly filtered columns, and so on.

Modeling guidance

• Start in the Power BI Desktop.
• Avoid complex queries in Query Editor.
• Don't use relative date filtering in the Query Editor.  
• Keep measures simple initially, and add complexity incrementally.
• Avoid relationships on calculated columns and unique identifier columns.
• Try setting “Assume Referential Integrity” on relationships – in many cases, this setting significantly improves query performance.  

General

• Apply filters first.
• Consider switching off interaction between visuals, which reduces the query load when users cross-highlight.
• Limit the number of visuals and the data per visuals, as described above.
• Enabling row-level security can result in large changes in performance. Be sure to test the different row-level security roles that your users will assume.
• There are query-level time-outs enforced by the service to ensure that long-running queries can't monopolize system resources. Queries that take longer than 225 seconds, time out and result in a visual-level error.

For further information, you may refer to the following link.

https://docs.microsoft.com/en-us/power-bi/power-bi-reports-performance

Best Regards

Allan

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