Dynamic filtering to create multiple columns

[Anonymous]

I have a set of data (Table 1) that looks like this:

I have another table (Table 2) that looks like this:

1. The codes in the "Type A" and "Type B" columns of Table 2 correspond to the codes in the column "Code" in Table 1.  I am trying to sum the "Data Point" values based on these corresponding codes between the two tables. 

2. I also need to apply the filters "Investment/Settlement", "TypeA/B" and "Outcome" to get the appropriate sums in the respective cells.

3. The codes follow a hierarchy structure, therefore some of the codes such as "LMN500" and "QRST600" are the uppermost parents. 

4. Row Header 1 (highlighted in blue) is the sum of the yellow rows beneath (Row A - Row D with filter "Investment"), and likewise for Row Header 2 (sum of Row A- RowD with filter "Settlement"). 

My end goal is to get an outcome like this (where the Xs refer to the calculated numbers):

5. Because multiple columns cannot have a relationship with a single column in another table, I've though of creating 3 seperate tables (2a, 2b and 2c) like this and then combining columns 1, 2 and 3 to get the desired result as displayed above. 

Therefore:
6. Given the above conditions - I need a bit of help with the appropriate DAX formula to calculate columns 1, 2 and 3. Considering the hierarchy structure in place for the codes, I suspect the DAX formula may use the function "CONTAINS" to search for the corresponding code in the "Merged Column" column in Table 1.  Open to any other suggestions!! 

7. For what I'm trying to achieve - is this an effient/approrpiate manner of going about it? I have c. 50 other tables that follow a similar structure and would need to apply the same method to them. Ultimately I want to be able to show the results using a Table visual in the reporting view - open to any suggestions, I am pretty new to Power Bi 🙂 

Thank you for your time!!

[Anonymous]

HI @Anonymous 

I would unpivot the table 2 on column 2,3 and 4 instead of creating three seperate tables so that my DAX formula becomes easier to manage. Also, if you have 50 other table you may consider using one column at the begining which says the table number.

[Anonymous]

@Anonymous Thank you very much for taking the time to respond, however this isn't what I was looking for! 🙂