Show more than 1 Million Rows in Direct Query

[Anonymous]

Hi All,

 I have a Power BI Report connected to an Azure SQL DB through direct query and hosted in a premium capacity Workspace and embedded in a Web Application.
The Report contains some KPI's at the top which shows the count / sum of certain columns in a table, and below it the table is shown as it is, ie a table visual.
We have many on load default filters being applied to the embedded Report in the application using the JS API's which drastically reduces the no. of rows being returned.

These filters are user editable and on the odd chance all the filters are removed by the user, we get an error : "The resultset of a query to external data source has exceeded the maximum allowed size of '1000000' rows".
I Tried by keeping an TOP N visual level filter for the table alone, but it didn't work. This cannot be done at SQL side as the KPI's at the top also refer this table and is also affected by all the filters in the application.

Anyway to overcome this?. Can we take advantage of Premium Capacity.

[amitchandak]

@Anonymous , refer if this can help

https://www.youtube.com/watch?v=4kVw0eaz5Ws

[Anonymous]

Hi @amitchandak ,

Thanks for the Answer, I'm going to create a view for only the columns required by PowerBI.
But that still does not fix the issue of the row limit.

[v-lionel-msft · Accepted solution]

Hi @Anonymous ,

"There's 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."

Try to optimize this query, such as using filters, slicers, etc.

Best regards,
Lionel Chen

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