Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II
Helper II

Combine 3 tables in DAX

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 1from table 2from table 3
Community Support
Community Support

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=
    '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]))
    _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





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!

Super User
Super User

hi, @Artefact 

try to use intersect() function

you nedd intersect twice for combine 3 table

Helper II
Helper II

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

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.


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.

New Member

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.

Helper II
Helper II

The result is the table in the post

Super User
Super User

show what result you expect

Helpful resources

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.


Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors