Memory Allocation Failure - Merge Two Tables

[Anonymous]

Hello,

Please help! I have a table that I created using DAX and I need to merge it with a table that I imported from a separate data source. I keep getting the Memory Allocation Failure - Try Reducing the Number of Queries error. I have done everything I can to reduce the table size by deleting columns and disabled the loading for a few queries, but when I use GENERATEALL() I get the error still. I also looked for other ways I could reduce query size by manually selecting a slicer on, turning off auto date/time intelligence, etc. I currently have 10 sources that I have to use. I am using 32-bit, but it is not possible for me to upgrade to 64-bit at this time at my job.

Does anyone know of a way that I can merge the table I wrote and the one I imported without triggering a memory failure?

I tried running the table and then putting it in excel which then I was able to merge with the other query, but this isn't sustainable for me to refresh. Looking for other ideas if possible! 

Thanks!

[artemus]

Why do you need to create a table usig GENERATEALL()? That will create a row for every entry in table ones TIMES every entry in table 2. So if you have a million rows in table 1 and a million rows in table 2 your end table will have 1 trillion rows.

Why not jut create a table that is the distinct values of the key column in both tables. E.g. DISTINCT(UNION(DISTINCT(Table1[KeyColumn]), UNION(DISTINCT(Table2[KeyColumn])))

[Anonymous]

I'm not sure I understand... can you please clarify?

Union needs to have the same columns, which these two tables do not. I need all of the values from Table 1 and matching values from Table 2, so I'm not sure if just pulling the key columns will work for me. The data I am using is customer-based so one customer can have multiple sales or no sales at all. I need to be able to determine which customer has which, and the values that I am working with are customer number, and the amount they've spent with us, so there really isn't a good key column to go off of unless I did an index, but that wouldn't give me what I want either.

https://docs.microsoft.com/en-us/dax/union-function-dax

[artemus · Accepted solution]

DISTINCT(Table[Column]) return a table with a single column of distinct values for that column. Thus the UNION statement will match schemas (as long as the key column names are the same) since it is just one column from each table. The outer DISTINCT removes one of the copies of a customer if it is present in both tables.

That is this should give you a single column table which contains all the customers listed in either or both tables. When you have this it should automatically connect it up with your other tables, and as long as you use the column in that table and aggergate the columns in the other tables you should be able to show a table in the UI that has columns from both tables.

[Anonymous]

Thank you! I think it works now! I appreciate your time 🙂