Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
Hi @v-lili6-msft ,
I published the file to common workspace and getting below error:
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
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
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
Hi @v-lili6-msft ,
I published the file to common workspace and getting below error:
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:
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