direct query limitation?

[katherine2999]

https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-use-directquery/

In this article, it mentioned  "There is a 1 million row limit for returning data when using DirectQuery", is that true? if so, what does it mean exactly?

[v-ljerr-msft · Accepted solution]

Hi @katherine2999,

There is a 1 million row limit for returning data using DirectQuery. This does not affect aggregations or calculations used to create the dataset returned using DirectQuery, only the rows returned. For example, you can aggregate 10 million rows with your query that runs on the data source, and accurately return the results of that aggregation to Power BI using DirectQuery as long as the data returned to Power BI is less than 1 million rows. If more than 1 million rows would be returned from DirectQuery, Power BI returns an error.

---

Yes, that's true. That is to say, you cannot return more than 1 million rows from your data source, then process those rows on Power BI side. But you can make aggregations or calculations against more than 1 million rows with the query which will run on the data source side, and return the result to Power BI side.

Regards

[deangelis91]

Hi,

can I make calculations with the query which will run on the data source side?

Thank you in advanced

[tringuyenminh92]

Hi @katherine2999,

When users interactive with charts/sliders, (query folding) the sql query will be generated and passed to your database in server side. This query will return result with maximum 1mil rows(something like select top 1.000.001 ... from.. where ..).