Combine 3 tables in DAX

[Artefact]

I have to combine a colum from 3 separate tables into one table with all combinations :
So this must be the end result
So it must only be the combinations which are present in the data, NOT ALL possible combinations, so CROSSJOIN alone doesn't do the job in my opinion.

from table 1	from table 2	from table 3
A	A	A
B	B	null
C	null	C
null	D	D
E	null	null
null	F	null
null	null	G

[v-yangliu-msft]

Hi  @Artefact ,

Here are the steps you can follow：

1. Create an Index for each table..

In Power Query -- add Column – Index Column – From 1.

2. Create calculated table.

Table =
var _table=
SUMMARIZE(
    'Table1','Table1'[Index],'Table1'[from table 1],
    "from table 2",LOOKUPVALUE('Table2'[from table 2],'Table2'[Index],'Table1'[Index]),
    "from table 3",LOOKUPVALUE('Table3'[from table 3],'Table3'[Index],'Table1'[Index]))
return
SUMMARIZE(
    _table,[from table 1],[from table 2],[from table 3])

3. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Artefact]

tb1	tb2	tb3
A	A	A
B	B	C
C	D	D
E	F	G

Hello Liu,
Thanks for your reply. Almost done, but I presented my initial tables somewhat different as intended. The null values aren't exactly on the rows as in the first post. The null values are not there at all, I just wanted to show the nulls in the end result. So the table above is the right starting point for obtaining the result table. Hope thsi helps, and thanks again for your efforts!

[Dangar332]

hi, @Artefact 

try to use intersect() function

you nedd intersect twice for combine 3 table

[Artefact]

addendum :

So basetable 1 has a column with only ABCE, table 2 ABDF and table 3 ACDG.
I don't want a cartesian product with CROSSJOIN, that would result in a much bigger table

[Lorenzo994]

Ok, now I get what you want. You want a Full Outer Join.

Unfortunately, DAX does not support this kind of join operation as far as I'm aware, so if you can't push this operation to Power Query (which indeed supports Full Outer Joins Full outer join - Power Query | Microsoft Learn) then the only solution that comes to my mind is a not so elegant workaround, which is the following:

1. Do a NATURALLEFTOUTERJOIN between Table1 and Table2, let's call it Join1
2. Do a NATURALLEFTOUTERJOIN between Table2 and Table1 (the order is important), let's call it Join2
3. Do first an UNION and then a DISTINCT between Join1 and Join2, let's call the resull FinalJoin
4. Repeat this process between FinalJoin and Table3

Let me know if it works for you.

[Artefact]

Thanks Lorenzo, seems like your idea works, but I need the full code for your solutions. I don't understand were I put the UNION and DISTINCT functions. Seems like these functions don't work with 2 (distinct) or 3 (union) columns in my code.

[Lorenzo994]

Hi, it's not very clear what is the starting point and what is the end result.

What is the criteria to join the table? You are saying in the same message that you expect all possible combination and right after that you do not want all possible combinations, so it quite hard to understand what you are looking for.

[Artefact]

The result is the table in the post

[Ahmedx]

show what result you expect