cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Direct Query 1 million row limitation in Power BI Premium workspace

Hello eveyone,

 

I am getting 1 million row record count error in Power BI Desktop when I am connecting to datasource through direct query.

 

But when the same pbix file I publish to PBI Service Premium workspace, I no longer see this 1 million row error.

 

The question here is - Does this 1 million row limititaion in direct query is not there for Premium worskspaces?

 

I tried to find the official Microsoft Document on this, but didn't get any.

 

Can someone shed some light on this please?

 

Thanks in Advance!

 

Regards,

Aniket

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Frequent Visitor

Re: Direct Query 1 million row limitation in Power BI Premium workspace

Hi @v-lili6-msft ,

 

I published the file to common workspace and getting below error:

error2.JPG

 

However, when I remove certain columns, I could see the data and the error vanishes and when sorting the table in descending order based on index column I could see index column showing 5 million records (i.e. the visual shows more than million records in PBI service).

 

Based on this analysis, we can say that 1 miilion row error in direct query is applicable to PBI Desktop only and not for PBI service.

View solution in original post

5 REPLIES 5
Highlighted
Community Support
Community Support

Re: Direct Query 1 million row limitation in Power BI Premium workspace

hi @aniketdolas 

Limit of one million rows returned on any query: There is a fixed limit of one million rows placed on the number of rows that can be returned in any single query to the underlying source. This 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 is not fully optimizing the queries sent, and there is some intermediate result being requested that exceeds the limit. It can also occur whilst 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.”

https://docs.microsoft.com/en-us/power-bi/desktop-directquery-about#other-implications

This error only in the power bi desktop, and in power bi service, it will not show this error but only return 1 million rows result in visual.

 

In a DirectQuery mode, your Power BI Report sends SQL query per each visual element (tile) separately.

 

For instance, you have a table where show two fields: City and SalaryUSD. In the same time, your SQL table with cities data has 20 million rows. If you drop to visual element (table) City and not aggregated SalaryUSD then Power BI will send a query like below

select City, SalaryUSD
from cities

which will return 20 million rows and will try to show it via table visual in Power BI, but it has a limit of 1 million rows to render.

 

And if you are able to change SQL query and do aggregation like below

select City, sum(SalaryUSD) as SalaryUSD
from cities

and in case result output of this query will have less than 1 million rows then it will be rendered on the report.

 

So this limitation has nothing to do with the license.

 

Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Frequent Visitor

Re: Direct Query 1 million row limitation in Power BI Premium workspace

Hi @v-lili6-msft ,

 

Thanks for your reply. Really appreciate your help!

 

Power BI Desktop shows error after exceeding 1 million row count and Power BI service does not show error but shows first million records only.

 

Is it correct? and if not could you correct it please?

 

 

 

Highlighted
Community Support
Community Support

Re: Direct Query 1 million row limitation in Power BI Premium workspace

hi @aniketdolas 

To my knowledge, it works as it., you could try to publish this report to a common workspace instead of premium workspace to have a try.

 

Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Frequent Visitor

Re: Direct Query 1 million row limitation in Power BI Premium workspace

Hi @v-lili6-msft ,

 

I published the file to common workspace and getting below error:

error2.JPG

 

However, when I remove certain columns, I could see the data and the error vanishes and when sorting the table in descending order based on index column I could see index column showing 5 million records (i.e. the visual shows more than million records in PBI service).

 

Based on this analysis, we can say that 1 miilion row error in direct query is applicable to PBI Desktop only and not for PBI service.

View solution in original post

Highlighted
Community Support
Community Support

Re: Direct Query 1 million row limitation in Power BI Premium workspace

hi @aniketdolas 


@aniketdolas wrote:

Hi @v-lili6-msft ,

 

I published the file to common workspace and getting below error:

error2.JPG

 

However, when I remove certain columns, I could see the data and the error vanishes and when sorting the table in descending order based on index column I could see index column showing 5 million records (i.e. the visual shows more than million records in PBI service).

 

Based on this analysis, we can say that 1 miilion row error in direct query is applicable to PBI Desktop only and not for PBI service.


It's pleasant that you could test and share the results with us 😁please accept the reply as solution, that way, other community members will easily find the solution when they get same issue.

Thank you for your help. 

 

Regards,

Lin

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

Helpful resources

Announcements
Super Users of the Quarter - Q2 2020

Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!

June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors