"Compressed Stream" error with Analyze in Excel

[bruceandersonBI]

We are using the Analyze in Excel addin with a certain report, pulling the data from service.  This report as around 500000 records.  There are about 80 fields we need to add in a flattened pivot style to make one large table.  We are finding there seems to be a limit to the number of fields we can add, until the attached error occurs.  I am wondering if this is a limitation with using Pro licenses, or something else. 

[lbendlin · Accepted solution]

Try Export to Excel with live connection and see if you get the same error.

[bruceandersonBI · Accepted solution]

So this one is a bit beyond my DX skills (lack thereof).

I stripped it back so I can get the syntax down to just two columns:

DEFINE VAR __DS0Core =SUMMARIZE('WCO','WCO'[No.],'WCO'[MHI Claim Date]) VAR __DS0BodyLimited = TOPN(500000,__DS0Core,'WCO'[No.],1,'WCO'[MHI Claim Date])EVALUATE __DS0BodyLimited ORDER BY 'WCO'[No.], 'WCO'[MHI Claim Date]

I changed it to this:

DEFINE VAR __DS0Core =SELECTCOLUMNS(SUMMARIZE('WCO','WCO'[No.],'WCO'[MHI Claim Date]),'WCO'[No.],"No.",'WCO'[MHI Claim Date],"Claim Date") VAR __DS0BodyLimited = TOPN(500000,__DS0Core,'WCO'[No.],1,'WCO'[MHI Claim Date]) EVALUATE __DS0BodyLimited ORDER BY 'WCO'[No.], 'WCO'[MHI Claim Date]

But then I get

Pretty sure I just have a peice of the syntax in the wrong spot.

[lbendlin]

you got the SELECTCOLUMNS syntax backwards.

EVALUATE SELECTCOLUMNS(SUMMARIZE ( 'WCO', 'WCO'[No.], 'WCO'[MHI Claim Date] ),"No.",[No.],"Claim Date",[MHI Claim Date])

[lbendlin]

In Excel, right click on one of the values then Table...Edit Query. The Command Text will have the DAX query that you can modify (among other things you can remove the silly 500K limit).  Encaplsulate the existing query with SELECTCOLUMNS and specify your desired colunm names.

[bruceandersonBI]

Many thanks!  I have no error when exporting to live.  However, I see live option is not available  for "Underlying Data".  So I will need to add all of the fields into the BI report (80+) so they come out in the summarised option, hopefully.  I will try that. 

[lbendlin · Accepted solution]

Try Export to Excel with live connection and see if you get the same error.

[bruceandersonBI]

That does get around the issue.  Nice and fast, no error. Only issue which is a new one is the table name gets appended to all columns in the live connection.  I wonder if there is any way to prevent that.

[lbendlin]

Yes, you can modify the DAX query via SELECTCOLUMNS.  But it will be quite tedious when you have so many columns.

[bruceandersonBI]

If it will work, I would embrace the tedious.  Ihave searched aroud for that but not finding anything in my resources with DAX on how to use SELECTCOLUMNS in that way.  Would greatly appreciate if you can point to how/where to do that?