cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Power BI how to check number of rows using direct query

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

5 REPLIES 5
v-janeyg-msft
Community Support
Community Support

@Anonymous  Any updates?

v-janeyg-msft
Community Support
Community Support

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 timeperformance 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
Super User
Super User

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
Frequent Visitor

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
Not applicable

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

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!