Forum Discussion
"Compressed Stream" error with Analyze in Excel
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.
Try Export to Excel with live connection and see if you get the same error.
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.
8 Replies
Try Export to Excel with live connection and see if you get the same error.
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.
Yes, you can modify the DAX query via SELECTCOLUMNS. But it will be quite tedious when you have so many columns.
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.
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.
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.
you got the SELECTCOLUMNS syntax backwards.
EVALUATE SELECTCOLUMNS(SUMMARIZE ( 'WCO', 'WCO'[No.], 'WCO'[MHI Claim Date] ),"No.",[No.],"Claim Date",[MHI Claim Date])