Manage Relationship between two tables without unique values

[mahra-in]

Hi

I need to manage a relationship with two tables

Table 1

Region	Spend
ASEC	54555
EMEAI	64363
EMEAI	45455
ASEC	5554
AMER	2254
ASEC	2244
AMER	55558

Table 2

Region	Savings
EMEAI	500
ASEC	250
ASEC	200
AMER	150
EMEAI	100
AMER	444

Result:

Region	Spend	Savings
AMER	57812	594
ASEC	62353	450
EMEAI	109818	600
Total	229983	1644

Visual shall be

Please help minimum on how to relate these two tables

[v-yulgu-msft · Accepted solution]

Hi @mahra-in,

You need to create a bridge table named as 'Table3'.

Table3 =
UNION (
    VALUES ( Table1[Region] ),
    EXCEPT ( VALUES ( Table2[Region] ), VALUES ( Table1[Region] ) )
)

Create a one-to-many relationship from 'Table3' to 'Table1' and 'Table2'.

Then, drag 'Table3'[Region] and 'Table1'[Spend] and 'Table2'[Savings] to visual.

Best regards,

Yuliana Gu

[mahra-in]

What if I have multiple colums and want to create relationship between table 1 & table 2.

Do I have to create bridge table for all columns separately to pull savigs & spend with respect to Region, Country or Entity?

Table 1

Region	Country	Entity	Spend
ASEC	Australia	Entity 1	54555
EMEAI	Denmark	Entity 3	64363
EMEAI	India	Entity 2	45455
ASEC	China	Entity 1	5554
AMER	Bethlehem	Entity 3	2254
ASEC	Australia	Entity 5	2244
AMER	Salt Lake City	Entity 4	55558

Table 2

Region	Country	Entity	Savings
EMEAI	India	Entity 2	500
ASEC	China	Entity 1	250
ASEC	China	Entity 1	200
AMER	Salt Lake City	Entity 4	150
EMEAI	Denmark	Entity 3	100
AMER	Salt Lake City	Entity 4	444

[mahra-in]

Is there a way to remove blank or null rows in the bridge table

[RolandsP]

You need to create a Bridge table with only one column that contains unique Region values. Then you can link Table1 and Table2 through the Bridge table.