Power BI how to check number of rows using direct query

[Anonymous]

Hi all,

Understand that Power BI Direct Query have limitation of one-million-row. How can I check in Power BI current visuals in dashboard have loaded how many rows data via direct query?

For example dataset A in datawarehouse for 4 months data we have 76 million rows, however Power BI dashboard only show 1 month data and have applied few filters as dashboard default view. We tried to query from datawarehouse with same filter applied in dashboard which obtain 700k rows.

Can we conclude that dashboard default view with filtered also query 700k rows of data in Power BI direct query mode? If not kindly advise how should I check number of rows loaded using direct query method.

Thanks in advance.

Yit Chuen

[v-janeyg-msft]

@Anonymous  Any updates?

[v-janeyg-msft]

Hi, @Anonymous 

There is such a sentence in the document: 

• Limit of 1 million rows returned on any query: 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. This limit generally has no practical implications, and visuals themselves aren't going to display that many points. However, 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. It can also occur while building a visual, on the path to a more reasonable final state. For example, including Customer and TotalSalesQuantity would hit this limit if there were more than 1 million customers, until some filter were applied.

The error that would be returned would be: "The resultset of a query to external data source has exceeded the maximum allowed size of '1000000' rows."

Reference: Using DirectQuery in Power BI - Power BI | Microsoft Docs

So the number of rows is abstract in DQ, and there is no need to entangle specific figure.

Tools that can be analyzed can only view the duration time: performance analyzer and SQL Server Profiler tool.

Best Regards

Janey Guo

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

[AbhiSSRS]

Hi! 

I just saw this study posted by Chris Webb on Linked in and thsi might help explain why you see 700K.

https://blog.crossjoin.co.uk/2021/05/02/measuring-directquery-performance-in-power-bi/

Relevan portion From his blog : 

The reason is that, at the time of writing at least, the Analysis Services engine can only push a top(n) operation down to a DirectQuery SQL query in very simple scenarios where there are no measures and no aggregation involved – and in this case we’re summing up values. As a result, if you’re using DirectQuery mode and have a visual like this that can potentially display a large number of rows and includes a measure or aggregated values, you may end up with slow performance.

Thanks!

Abhinav

[rakesh_js]

Hi @Anonymous 

I'm Thinking,

the row count is under the admin-set limit.

As an admin, you can find these new settings on the Admin portal> Capacity settings page , expand the “Workload” on the management tab of the capacity.

Regards,

Rakesh

[Anonymous]

Hi @rakesh_js 

Thanks for the reply.
However my Power BI account is Pro account, capacity settings is only available for Premium account.

Regards,

Yit Chuen