Analyze in Excel Error: MaxPacketLimitReached

[DataNat]

I am analyzing a report in Analyze in Excel and there is one numeric field that triggers the error copied below. Other fields are pulled into the pivot table without issues.

We couldn't get data from an exernal data source. Please try again later.

If the problem persists, please contact the administrator for the external data source.

Here is the error message that was returned from the Analysis Server named pbiazure://api.powerbi.com:

{"error:"{"code":"DM_GWPipeline_Client_MaxPacketLimitReached","pbi.error":"code":"DM_GWPipeline_Client_MaxPacketLimitReached","parameters":{},"details":[],"exceptionCulprit":1}}}

For context, I am using a DirectQuery connection to a Sql server database.

I did a search for this error but wasn't able to find any results. Anyone know what may be going on?

[GilbertQ]

Hi there

From the message it would indicate that the amount of rows that it will request is more than the default (which from my understanding is 1 million rows)

If you could confirm if you run a query on the SQL database for that column how many rows it returns?

[Element115]

I have the exact same issue but it comes about when I am running a DAX query from Report Builder. 

The query references tables from a Power BI model in the Power BI Service, ie cloud.  The model is hybrid in the sense that all tables are in import mode but one, which is is in DirectQuery mode via a gateway to an on-prem SQL Server DB (2017).

So, the DAX that Report Builder is trying to run references both the DirectQuery table and imported tables.  The DirectQuery mode cannot be imported because there is simply too much data to import the table as a whole. 

The error message is exactly the same:

{"error:
"{"code":"DM_GWPipeline_Client_MaxPacketLimitReached",
"pbi.error":"code":"DM_GWPipeline_Client_MaxPacketLimitReached",
"parameters":{},
"details":[],
"exceptionCulprit":1}}}

The initial working result set size the DAX query needs to work on is < 900K.  I am trying to get DAX to further filter it down to a couple dozen rows:  It works fine in DAX Studio, but when running from Report Builder it craps out. I suppose Report Builder uses a DAX engine inside its own process and tries to get all the initial data it needs to work on instead of telling the DAX engine in the cloud, ie in the Power BI Service, to perform the computation and once finished send the smaller result set.

Of note is that the result set DAX starts with from the DirectQuery of the DB never exceeds the 1M row limit. Therefore why does Report Builder emit this error????

None of it makes any sense. But hey, I guess I should start getting used to the fact that not much makes sense when it comes to DAX, especially its inability to refer to table variable names that are supposed to be in scope but won't be recognized as existing because only a base table ref will do.  What a wonderful DSL (Domain Specific Language)!  

[CodyB2018]

I also have the same issue, Did you ever have any luck with this?

[Element115]

I can't remember TBH, it was such a long time ago.  But after this initial testing with Report Builder, we determined it was too horrible an experience to use in production and so we switched to InfoRiver Matrix.  It was like going from hell to heaven, I kid you not--Report Builder user experience is that bad.

[DataNat · Accepted solution]

Thanks GilbertQ for your input! This isn't the direct solution to the issue, but I switched to Import mode (from Direct Query) and the error is gone. This does appear to be a row limit issue, specifically in direct query mode.