cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted

Memory allocation error on DAX table UNION 50M+ rows

Trying a quarterly csv file based data refresh and then doing a DAX table union join of old quarters and new quarters - I now get a memory allocation error.

Queries to the csv files load correctly.

csv files are 2.5GB per quarter , resulting table is 10M rows per quarter. 5 quarters loads OK , 6th quarter creates error when doing DAX union. 

This operation worked in April (5 quarters of data / 50M rows) - but now doesn't for the June quarter

32GB RAM worked OK last time , and appears to have capacity when viewing performance test on query stage.

Latest July PBI desktop 64 bit installed (7/12/2017) Version: 2.48.4792.721

Is there any other limitation that could be throwing this error ?

Thanks

6 REPLIES 6
v-huizhn-msft Super Contributor
Super Contributor

Re: Memory allocation error on DAX table UNION 50M+ rows

Hi @pat_energetics,

You can disable the option: allow data preview to download in the background (highlighted in yellow backgroud). Please review this article.

1.PNG

Memory usage is related dataresouce, the Power Query steps, computer configuration and so on. Please see the following blogs for references.

 

Performance Tip for Power BI; Enable Load Sucks Memory Up.

Best Regards,
Angelia

Re: Memory allocation error on DAX table UNION 50M+ rows

Unfortunately this tip did not resolve the issue.

 

Now trying a different order and size of query to see if the issue is in the way in which queries are appended into one before loading.

 

Any other ideas appreiciated.

Re: Memory allocation error on DAX table UNION 50M+ rows

It now appears that no matter what combination of appending queries I try , I get the following error ....

 

Formula.Firewall: Query 'Bid_Vol_History' (step 'Appended Query1') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.

 

According the the query dependency view , the sub queries being appended into the final query are correctly linked to their respective (csv file) data source.

v-huizhn-msft Super Contributor
Super Contributor

Re: Memory allocation error on DAX table UNION 50M+ rows

Hi @pat_energetics,

If you did any operation in Query statement? How about you reload the data?

Best Regards,
Angelia

Re: Memory allocation error on DAX table UNION 50M+ rows

Tried that several times with reload / restructure queries / reorder queries - all without success unfortunately....

dkay84_PowerBI New Contributor
New Contributor

Re: Memory allocation error on DAX table UNION 50M+ rows

It might be a better choice to do your joins/appends within the query editor and not via DAX.  The memory usage and compression will be more performant that way.

 

As for the firewall error, this is a well documented issue.

 

https://blog.crossjoin.co.uk/2017/06/26/data-privacy-settings-in-power-bipower-query-part-3-the-form...

 

https://www.excelguru.ca/blog/2015/03/11/power-query-errors-please-rebuild-this-data-combination/