This default limit of 1 million rows is the same used for models created by Power BI Desktop. The resultset of a query to external data source has exceeded the maximum allowed size of ‘1000000’ rows. When this happens, SSAS assumes that there are other rows that have not been transferred, and the result based on this incomplete result would be incorrect.

If the result is greater than 1 million of rows, the number of rows transferred is exactly one million and one. However, a TOP clause limits the number of rows that could be returned by this query to 1 million: The SQL query that is generated does not execute the COUNT operation on SQL Server, and transfer the list of existing combination of Order Number and Order Line Number values to Analysis Services. For example, consider this other DAX query:ĪLL ( Sales, Sales ) However, other DAX queries might transfer a large number of rows to Analysis Services for a following evaluation. It generates a corresponding SQL query that returns only one row: The reason of this behavior is to limit queries that could run too long, requesting too much memory on Analysis Services to store an intermediate result during a more complex query.įor example, consider the following DAX query: In practice, DirectQuery replaces VertiPaq as a storage engine in the Analysis Services architecture, as you see in the following schema.Įvery time DirectQuery generates a query to SQL Server, it only retrieves a predefined maximum number of rows from SQL Server, which is 1 million by default. When you use DirectQuery, the VertiPaq engine does not store a copy of the data in memory, and you do not have to process the data model in advance. When you create a data model in DirectQuery mode, you can include in the data model only tables from a single relational database, and every MDX or DAX query generates one or more SQL query to the source database. From hereinafter, the article describes the architecture in Analysis Services, but all the concepts are identical in Power BI.

Sql server limit rows returned