Direct query mode error

[Happy2023_05]

Hello All,

I get the below error when connecting live dataset and another table as direct query mode for a report.

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

Can you pls provide any suggestion to fix this?

[v-yanjiang-msft · Accepted solution]

Hi @Happy2023_05 ,

Yes, it's a limitation with DirectQuery mode, refer to the official document: Use DirectQuery in Power BI Desktop

---

There's a one-million-row limit for cloud sources (which is any data source that is not on-premises), with on-premises sources limited to a defined payload of about 4 MB per row (depending on proprietary compression algorithm) or 16MB data size for the entire visual. Certain limits may be raised when using Premium capacity. The limit doesn't affect aggregations or calculations used to create the dataset returned using DirectQuery. It only affects the rows returned. Premium capacities can set maximum row limits, as described in this post.

For example, you can aggregate 10 million rows with your query that runs on the data source. The query accurately returns the results of that aggregation to Power BI using DirectQuery if the returned Power BI data is less than 1 million rows. If over 1 million rows are returned from DirectQuery, Power BI returns an error (unless in Premium capacity, and the row count is under the admin-set limit).

---

Best Regards,
Community Support Team _ kalyj

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

[amitchandak]

@Happy2023_05 , This means direct query is getting more than 10M rows. You need to simply the meausres.

refer: https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-directquery-about#other-implications

[Happy2023_05]

Thanks for the response.

I could not understand this. 

You need to simply the measures.

So there is limitation in direct query that it can fetch only 1M rows. Any solution for this?