Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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
v-lili6-msft
Community Support
Community Support

hi @Anonymous 

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

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?

 

 

 

hi @Anonymous 

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

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.

hi @Anonymous 


@Anonymous 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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors