Forum Discussion
Paginated Report
Hello everyone,
I have a Paginated Report that works just fine on the Desktop App but if i publish the report to the powerbi Service and want to use it, i get the error message,
"Unable to render paginated report A data source used by this report returned an error. Received error payload from gateway service with ID 57703: Async operation a1ee1d60-b03b-4159-b802-8d902e4eef69 is faulted with [0]Microsoft.PowerBI.DataMovement.Pipeline.Diagnostics.AdomdDataAccessErrorResponseException: AdomdException encountered while accessing the target data source. GatewayPipelineErrorCode=DM_GWPipeline_Gateway_DataSourceAccessError GatewayVersion=3000.242.9 ErrorCode=-1056308850 --->
[1]Microsoft.PowerBI.DataMovement.Pipeline.Diagnostics.GatewayPipelineWrapperException: Substituted: AdomdErrorResponseException:The specified query is too complex to be evaluated as a single statement. GatewayPipelineErrorCode=DM_GWPipeline_UnknownError GatewayVersion=3000.242.9 InnerType=AdomdErrorResponseException InnerMessage=The specified query is too complex to be evaluated as a single statement. InnerToString=Microsoft.AnalysisServices.AdomdClient.AdomdErrorResponseException: The specified query is too complex to be evaluated as a single statement.
"
I get the error message when i select my filters and then click on view report. If i want to get only one customer at the filter the reports works but when i want to see all customers at one i get the error. In the Desktop App "Power BI Report Builder" i dont get this issue at all.
How can i resolve this error? Any suggestions?
17 Replies
It means the DAX query generated by the Paginated Report when you select all customers exceeds what the AS engine can process as a single query.
Option 1) Pre-aggregate the data upstream
The complexity usually comes from the DAX query trying to resolve too many measures across too many members simultaneously. Move heavy aggregations into a dedicated table in your semantic model or into a Fabric Lakehouse/Dataflow so the Paginated Report queries pre-computed results rather than raw grain data.
Option 2) Review and simplify the dataset query
Open the dataset in Report Builder → dataset properties → look at the MDX or DAX query. If it uses complex nested CALCULATE, multiple FILTER iterations, or row-level expressions across a large table, simplify or split into multiple datasets.
Hi LaurenzR , Thank you for reaching out to the Microsoft Community Forum.
I think this is happening because when you select all customers, the paginated report generates a much larger DAX query behind the scenes, usually expanding the parameter into a big filter list. That query becomes too complex for the Analysis Services engine to execute in the Power BI Service, especially through the gateway, even though it still works in Report Builder locally.
I suggest you don’t pass All as a multi value parameter containing every customer. Instead, handle All as no filter in your dataset query, so it doesn’t inject thousands of values or redesign the parameter to reduce the number of values being passed.
Hello, thank you for the help.
I use RRSCustomDaxFilter in the Dataset as a Filter. How can i configure the Parameter / the Dataset Query, so that all Items are selected but not in the Query that gets executed(As you mentioned "All as not filter in the dataset query").
Hi LaurenzR , Thank you for reaching out to the Microsoft Community Forum.
With RSCustomDaxFilter, you shouldn’t let it run when All is selected, because that’s what expands into a huge filter. I suggest you introduce an All option in your parameter (e.g., value = “ALL”) and then make your dataset query conditional: if the parameter = “ALL”, you skip RSCustomDaxFilter entirely (no customer filter applied); otherwise, you apply RSCustomDaxFilter as you do today. In other words, All should translate to no filter in the query, not a filter containing every customer, that’s what prevents the query from becoming too complex in the Service.
Hi LaurenzR , hope you are doing great. May we know if your issue is solved or if you are still experiencing difficulties. Please share the details as it will help the community, especially others with similar issues.
Hi v-hashadapu, unfortunately i did not solve the problem. Your tip with the Treatas statement sadly did not work, because Treatas need a column input an not a list of values. i tried to get the parameter values as a column by using Row(), but this did not work either. If it does not work at the next try i am just going to make two files. One with the Customer filter and the other without it.
Hi LaurenzR , thanks for the update. Please do keep us posted with the results, that may help others with similar issues.
Hi LaurenzR , Hope you're doing okay! May we know if it worked for you, or are you still experiencing difficulties? Let us know — your feedback can really help others in the same situation
