Conversion of formula to DAX / PowerPivot

Hi,

I am looking to convert an Excel formula to a DAX formula to be used in my PowerPivot data model.

My data set lists a number of investors and their portfolios' allocation towards different stocks.

Unfortunately, it is formatted something like this: Stock 1/5%/500,25:Stock 2/10%/1000,50:Stock 3/85%/8004, with "Stock name / proportion / amount", where the amount is irrelevant.

Currently, my Excel solution is along the lines of the following and can be found in the table "Solution":

HTML Code: 

=SUMPRODUCT(TEXT(SUBSTITUTE(MID($B3;FIND("^^";SUBSTITUTE($B3;$D$3:$D$14;"^^")&"^^")+LEN($D$3:$D$14&"/");2);"%";"");"0;0;0;\0")*($E$3:$E$14=E$18))

Converting each formula into DAX is simple (replacing the TEXT formula with the FORMAT formula). However, my problem is that I am not sure how to convert the column references to table "Type" into DAX.

I have tried with the ALL function, but without luck. So, the parts I need help fixing are the " 'Type'[Type] " parts (I have removed some of the extra code that has nothing to do with the issue):

HTML Code: 

=FORMAT(SUBSTITUTE(MID('Portfolios'[Stocks, proportion, and amount];FIND("^^";SUBSTITUTE('Portfolios'[Stocks, proportion, and amount];'Type'[Type];"^^")&"^^")+LEN('Type'[Type]&"/");2);"%";"");"0;0;0;\0")

I have previously been using a Power Query solution, but unfortunately the workbook has begun crashing occasionally due to the size of the real data sets and number of columns the PQ procedure runs through.

The sample workbook is attached here. Thanks!

I really wouldn't recommend doing this in DAX. As DAX gets re-evaluated with each interaction with the pivot table.

It will significantly slow down your performance.

Try following attached sample.

Then I'd recommend following method.

1. Add index column to your original table. Split into 2 separate tables, each having 1 column of strings. Make sure to carry over index column to each.
2. Create separate dimension table to relate the two tables using Index.
3. Perform transformation on both tables.
4. Then create Star Constellation Schema in data model.

This will be much more performant than DAX calculated columns. Number of stocks in each portfolio should not have too much impact on number of steps required.
By splitting fact table into two individual part, it will simplify transformation required and will reduce memory foot print.

DAX isn't really meant for this sort of calculation. If still having issues, I'd recommend uploading sample that is more representative of your actual data set. That way we can help you find optimized solution that will meet your need.

EDIT: Oh, if you have source data residing in the workbook with PowerQuery. I'd recommend separating them out. And query external workbook using PQ.