Summarize over columns from Different Tables

Anonymous

My raw original tables look like below

Table name:Black                                                            Table Name: White

P2     Region     Country       Value                                  Plan     Regionx   Countryx    Result

23x   ASPAC     Nord            2                                        7x         NA           JAP               2  

23x   EMEA       EAST            3                                        15x       NA           JAP               2  

18x   ASPAC     Nord            2                                        11x       NA           JAP               2  

17x   NA           JAP               2                                        11x       NA           JAP               2  

In my design I have used PowerQuery to

1. Create new table view and Group Table Black by P2 and Region

2. Create another new table view and Group Table White by P2 and Region

3. Append both 1. and 2 in another new table, this will be my brideg table connecting my raw data Black and White

4. Remove duplicates from 3,. All of these steps effecively will create a full outer join which is what I need.

5. Add a Third column and concatanate columns 1 and 2 to create a unique key

6. Create a one to many reationship from this unique key in this Bridge table to my orignal Black and White tables. (I also had to create the concatanated column in my original Black and white tables to make this link)

7. Now I  can write a Summarize DAX measure over the P2 and Region columns in the bridge table.

8. Lastly create another table of unique P2 from an APPEND of table Black and White. This is linked by a one to many relationship with P2 in my Black table and Plan in my white table.

As can be seen this is very heavy on the CPU processor since there are about 10 million rows.

I would like to understand if your way is infact simpler ? It does seem very light as you are only using a DAX function to summarize across 2 tables rather than create this bridge table which I have done ?

If your is indeed much lighter can you pls rewrite your DAX measure to match the column and table naming I have provided?

Why I made the bridge table ?

I wanted to create the following measure

ABS Error P2/Country = SUMX(SUMMARIZE('Bridge_table','Black'[P2_Region],'White'[PLan_Regionx]),ABS([Sum of Error]))

 Then in my table visualization I would like to display the following

P2(this is the result of Step 8)             Value (from Black table)              Result (from White)       ABS Error (measure above)