topic Create new table based on columns from related tables keeping all the values in the columns in DAX Commands and Tips

Create new table based on columns from related tables keeping all the values in the columns

Chris2016 — Fri, 22 Dec 2023 12:57:53 GMT

Hello,

I need help with the following issue:

I have two tables where the following columns exist:

TableA

Category	Product
A	Cars
B	Planes
C	Trains
D	Bicycles

TableB

Products	Color
Cars	Blue
Cars	Green
Cars
Planes	Blue
Planes	White
Planes
Trains	Blue
Trains	Black
Trains

I need to create a new table that contains three columns from the two tables, where all the values of all the columns are kept, i.e.:

Category	Products	Color
A	Cars	Blue
A	Cars	Green
A	Cars
B	Planes	Blue
B	Planes	White
B	Planes
C	Trains	Blue
C	Trains	Black
C	Trains
D	Bicycles

Any help in achieving this is much appreciated.

Thanks!

Re: Create new table based on columns from related tables keeping all the values in the columns

Fowmy — Fri, 22 Dec 2023 13:49:44 GMT

Chris2016

First create a relationship between table A and table b using the field products. Then create the following table to have the desired results as a table.

New Table = SELECTCOLUMNS( NATURALLEFTOUTERJOIN( TableA , TableB ), TableA[Category], TableA[Product], TableB[Color] )

Re: Create new table based on columns from related tables keeping all the values in the columns

Chris2016 — Fri, 22 Dec 2023 15:59:39 GMT

Thanks a lot for your help, Fowmy.

I do have a relationship like you mentioned:

While in my test pbix I have an error in the formula, the table is displayed as expected.

However, in my actual report I get this error:

The relationship I have between TableA and TableB is between the IDs of columns Product, but it is a many to many relationship. Is this what's effecting the NATURALLEFTOUTERJOIN function?

Thanks a lot!

Re: Create new table based on columns from related tables keeping all the values in the columns

Fowmy — Fri, 22 Dec 2023 19:08:37 GMT

Chris2016

Yes, Many to Many relationships will not work in this case. I am not sure what your are trying to achieve by mergeing these tables. However, please try to keep one to many relationship by having a relevent dimension table.