Trying to substitute merge query with a VLOOKUP equivalent in DAX, but getting multiple values ERROR

[jburbano]

I have a large dataset reload due to a Merge Query in Power Query.  The result is a table that I expand for a particular value.  Since Power Query does not have a native VLOOKUP capability, I was using a merge.  The data has gotten to large and I am just about over the 25Gb limit for memory in my P1 node.  As an alternative, I am trying to leave it to DAX to handle it as LOOKUPVALUE.  The issue is that the data sometimes is one value, works great, and sometimes is multiple values, errors out because it is expecting one value.  As such I want to feed the LOOKUPVALUE to a CONCAT or equivalent function to adds UNICHAR(10) to each value, so it is one string (one value) instead.  

How can I achieve what I am trying to do, either via Power Query, going back that route, or using DAX with LOOKUPVALUE and concating multiple values as one value?

[v-zhangti]

Hi, @jburbano 

Can you provide some sample data or simple pbix files? Sensitive information can be removed in advance. What kind of expected results do you expect? You can also show it with pictures.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[edhans]

You definitely want to use either Power Query or a data transformation further upstream, like a SQL View.

In Power Query, you select multiple fields for the join as shown shown below. You don't want to concatenate stuff.

Also by doing it in Power Query you are reducing the amount of data that actually comes into the model, thus reducing memory. If you do the LOOKUPVALUE route, you are bringing in the total of BOTH tables, and the "merged" values. That is the worst of all worlds.

Below I am using CTRL-CLICK on the 2nd and 3rd field in each table to do the multi-key join. You cannot do that in DAX at all.