Report with huge data not loading data in Power BI Desktop and PBI service

natyp

I think the million row limit is a limitation of Direct Query, but it does depend on how things are set up. You might need to look at the construction of what you've put together and maybe try to break it down. I think the idea with Direct Query is to use query folding if possible, or have the data already shaped and summarized if you can.

I've come across this limit before as well and had to think of a different design. You could try adding, (or starting with), a dummy table that is local to the report. If you then add direct queries, you should have the option for Mixed Mode. This combines Import and Direct Mode and may enable you to workaround the limitation. There are also settings related to memory in the Options pane that you could try adjusting. I know this isn't a specific solution but I hope it helps.

pbix1 : This behavior is expected when using DirectQuery. By default, Power BI enforces a 1 million row limit on the result set returned from an external data source in DirectQuery mode. When a visual attempts to retrieve more than this threshold, it can result in errors such as:

• The resultset of a query to external data source has exceeded the maximum allowed size of 1,000,000 rows

• rsQueryMemoryLimitExceeded

While this 1 million row limit can be altered at the service or capacity level if its premium , it is not recommended due to the significant impact it can have on performance, memory consumption, especially with large datasets. 
https://powerbi.microsoft.com/en-us/blog/five-new-power-bi-premium-capacity-settings-is-available-on-the-portal-preloaded-with-default-values-admin-can-review-and-override-the-defaults-with-their-preference-to-better-fence-their-capacity/

Other approaches

• Use slicers effectively to ensure the dataset is filtered before the query is executed, keeping the result set well under the 1 million row limit.
  Try to limit the data by using Date or appropiate dim instead of loading all the data 

• Avoid loading full-detail tables by default; ensure users must make a selection before data is queried.

• Create a summary or aggregated page as the landing view, and enable drill-through to a detailed page. This ensures only a targeted subset of data is queried when users explicitly request details.
  
  In one of the report I was facing the issue when we directly try to show the details of the transaction to over come this I created a summary page with filters from Date and other dimension and rolled it by the required dimension level and from that page I have enabled the drill to show the details of that transaction . 

That is the expected behaviour. What the table visual does is computationally expensive so it gets throttled. Use slicers instead and inform your users to use these to be able to see the data. Who would have the  time anyway to be scrolling through thousands if not millions of rows?

Hi natyp 

Thank you for posting your query in the Microsoft Fabric Community Forum, and thanks to pbix1 , Natarajan_M  and danextian  for sharing valuable insights.

Could you please confirm if your query has been resolved by the provided solution?If you have any more questions, please let us know and we’ll be happy to help.

Regards,

Microsoft Fabric Community Support Team.

Hi natyp 

Thank you for the update. If you have any more questions, please let us know and we’ll be happy to help.

Regards,

Microsoft Fabric Community Support Team