Forum Discussion
Read explained formula
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
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:
1 Reply
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: