Read explained formula

[Marco_88]

Hello everyone,

I've this problem.
I have a Table TAB1 with some columns TAB1_n, TAB1_A, TAB1_B, TAB1_C, TAB1_D, with some numerical data.
I have also a Table TAB2 with these columns TAB2_n, TAB2_A, TAB2_B, TAB2_C, TAB2_D, TAB_E, TAB_F.
I Created a new merged table called Merge1.

In Merge1 I added a new column called QTY that has:
- in case of column TAB2_A, TAB2_B... report an "X", PBI will copy in QTY column the corresponding data from TAB1_A, TAB1_B...
- in case of column TAB2_A, TAB2_B doesn't have an "X" value, it looks at column TAB_F if there is a formula. If it's true, it will calulate value from the formula based on Name of TAB1 and report results in QTY column.

like this:

My problem is that I can't get PBI to read the formula.
Can anyone help me?

Thank you in advance

Marco

[MFelix · Accepted solution]

Hi @Marco_88 ,

DAX cannot make this however you can do it using Power Query.

Your merge table should have the following format:

Has you can see I replaced the X by the values of each line.

Now do a merge with the Tab 1 on N and expand the table A, b, C ,D

Now add the following custom column:

Expression.Evaluate( Text.Replace(Text.Replace(Text.Replace(Text.Replace([FORMULA],"A",Number.ToText([A])),"B",Number.ToText([B])),"C",Number.ToText([C])),"D",Number.ToText([D])))

Now delete the columns ABC

Final result: