Forum Discussion
DirectQuery 1M row limit in paginated query
Hi GilbertQ,
I actually thought that using paginated report would implicitly do that - add filter to the underlying query and return only 10 rows for the first page. Maybe I misunderstood, and paginated report only provides spliting data to pages (basically client side pagination), and all dataset is always fetched.
I am creating this report from PBI web interface, where I just select few columns from a table in my Semantic model, and as soon as I add ContractNumber column, I get 1M records limit error. And after that, I can't even edit the report from web interface. So, I am not sure if I am able to put filters on direct query this way.
Also, this actually isn't inteded to be a fixed report - I need to create a basic report displaying contract number where other advanced users could add different measures in order to do ad-hoc analysis on a contract level. So we actually need to be able to view contracts from PBI web interface. Maybe PBI isn't intended for this purpose at all, as we are not aggregating anything, but I would like to make sure I really should look for alternative solution. And just to clarify, we do have a number on summary reports already in PBI, we just need this one contract level, on top of everything.
In DirectQuery mode, Power BI does not import data into its model, but queries the data source directly. This means that every interaction (such as adding a filter, selecting a column, or drilling down) sends a query to the underlying data source.
Because the underlying query generated by Power BI returns too many rows to exceed the limit of DirectQuery.
Paginated reports in Power BI also query the data source directly. Even if your query returns millions of rows, Power BI will still try to retrieve all rows before applying paging, which is why the row limit is reached.
Add filters directly to the SQL query or data source to limit the dataset before it reaches Power BI. This will help you avoid the 1M row limit.
You can create parameters in paginated reports to allow users to filter the data before retrieving it (for example, filter by a specific contract number or date range).
If you are using a Premium workspace, consider using Power BI dataflows to create pre-aggregated or filtered views of your data. These dataflows can be the source of paginated reports, reducing the amount of data returned.If I have answered your question, please mark my reply as solution and kudos to this post, thank you!
