Paginated Report

[LaurenzR]

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?

[v-hashadapu]

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.

[LaurenzR]

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.

[v-hashadapu]

Hi @LaurenzR , thanks for the update. Please do keep us posted with the results, that may help others with similar issues.

[v-hashadapu]

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.

[LaurenzR]

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"). 

[v-hashadapu]

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.

[LaurenzR]

Thanks for your help. I think i found a way to implement this solution into my Dax Dataset Query, but when i want to try it i get the Error: "RSCustomDaxFilter is not a valit table, variable or expression name". I have got this error also in the past but have not realy found why this happens and how to fix it

[v-hashadapu]

Hi @LaurenzR , 

RSCustomDaxFilter isn’t an actual DAX function, it’s a placeholder that Report Builder only recognizes in its original position in the query. If you try to wrap it in IF, use it in a variable or move it around, the engine can’t resolve it and throws that error.

So, instead of making it conditional in DAX, keep RSCustomDaxFilter exactly where it is and control it through the parameter. Add an ALL option and when that’s selected, pass a blank/Nothing value to the parameter so the filter doesn’t get applied. That way All behaves like no filter, without breaking the query.

[LaurenzR]

I tried checking if everything is selected and if so than ignore the parameter but you cant use conditions in combination with RSCustomdaxfilter so that did not work. also the all element did not work, because there would be 10 entries for "All" if there were 10 customers and when i made it that the All Element only appears once, then the RSCustomDaxfilter filters the dataset with the Value "all" and returns nothing because there is no such element in the base data.

i dont have any clue what else i could try

[v-hashadapu]

Hi @LaurenzR , Thank you for reaching out to the Microsoft Community Forum.

Separate the All case from the filtered case. Keep your current parameter for customers, but add a second flag parameter (e.g., Show All). Then in the dataset parameter mapping, pass the customer values only when filtering is needed and pass Nothing/blank when All is selected so no filter gets applied. If your setup doesn’t handle blank cleanly, use two datasets (one with RSCustomDaxFilter, one without) and switch between them, this avoids the issue entirely.

[LaurenzR]

But if i pass Nothing to the RSCustomDaxFilter Function, then i wont get any customers back or am i wrong

[v-hashadapu]

Hi @LaurenzR , yeah, my bad, try using one dataset/query with RSCustomDaxFilter for selected customers and another dataset/query without any customer filter for All, then switch between them using a parameter.

[LaurenzR]

Hi, 

I tried that. I havo two tables one for each dataset. if everything is selected in the customer filter i want to hide the oder table and if only a few / not all customrs are selected i want to hide the oder table. But the Problem is, that the Query to the Modell is still executed for both Datasets and i get the Error again

[v-hashadapu]

Hi @LaurenzR , I think the problem here is, as we discussed already RSCustomDaxFilter isn’t real DAX. It gets expanded by Report Builder into a long list of filter values. When you select all customers, that list becomes huge and can exceed what the engine can handle in one query and may fail in the Service. You also can’t make it conditional, if it’s in the query, it will always expand. So, I suggest you stop using it for this case and handle the filter directly in DAX, where All means no filter instead of passing thousands of values.

Pass your multi-value parameter as usual and then handle the filter directly in the dataset query using a pattern like this:

EVALUATE
VAR _SelectedCustomers = @CustomerParam
VAR _AllCustomersCount = COUNTROWS(ALL('Customer'[Customer]))
VAR _SelectedCount = COUNTROWS(_SelectedCustomers)
RETURN
CALCULATETABLE (
<your base query>,
IF (
_SelectedCount = _AllCustomersCount,
TRUE(),  -- All selected → no filter
TREATAS(_SelectedCustomers, 'Customer'[Customer])
)
)

[cengizhanarslan]

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.