Forum Discussion
Any best practice for using direct query connection?
Thanks aj1973 for your response. I want to understand the 1 million rows limiation a bit more.
I have a table with more than 70 million rows. The table has a column named 'price' and another column named 'date'.
When I wrote a measure basically just sum(price) and I'm able to put it in a card visual and display it without any issues. In this case, the underlying data is already exceeding 1 million rows, why does this measure work?
On another attempt. I put the `date` and `price` in a table and select `do not summarize` for the `price`, so in this case, my data again is exceeding 1 million rows, but the table show up okay.
Now, when I add the measure to the table, I'm getting the limitation error message. Just would like to understand why it happens at this time instead of the previous two attempts.
Thank you
Here's what is happening:
However, the limit can occur in cases where Power BI doesn't fully optimize the queries sent, and requests some intermediate result that exceeds the limit.
The limit can also occur while building a visual, on the path to a more reasonable final state. For example, including Customer and TotalSalesQuantity could hit this limit if there are more than 1 million customers, until you apply some filter. The error that returns is: The resultset of a query to external data source has exceeded the maximum allowed size of '1000000' rows.
I got it from here
https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-directquery-about
Hi aj1973, I have read the documents in the past. In the example you quoted, is TotalSalesQuantity a measure?
In my previous response, "On another attempt. I put the `date` and `price` in a table and select `do not summarize` for the `price`, so in this case, my data again is exceeding 1 million rows, but the table show up okay."
Isn't this result contradictory to the example from the documentation?