DirectQuery 1M row limit in paginated query

[stefankostic]

I am struggling to create a report that will enable users to view contract level information. I am using DirectQuery and working on premium per user license. I created a paginated report and tried displaying contract number and contract date when I got 1M row limit error. My underlying table really does have a couple of milions of records, but I thought that if I use paginated report, only 10 results would be queried. Or paginated reports fetches all results from the source and then does pagination internaly? If yes, what would be the approach to provide contract level reporting?

[stefankostic]

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.

[hackcrr]

@stefankostic 

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!

[GilbertQ]

Hi @stefankostic 

Due to using DirectQuery I would suggest putting a filter on the DirectQuery to only return a subset of the data, which should then not return all the rows?