Skip to main content
cancel
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

Reply
Artefact
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
AAA
BBnull
CnullC
nullDD
Enullnull
nullFnull
nullnullG
9 REPLIES 9
v-yangliu-msft
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.

vyangliumsft_0-1706691616156.jpeg

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:

vyangliumsft_1-1706691616157.png

 

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

tb1tb2tb3

A

AA
BBC
CDD
EFG

 

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
Super User
Super User

hi, @Artefact 

try to use intersect() function

you nedd intersect twice for combine 3 table

Artefact
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.

Lorenzo994
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.

Artefact
Helper II
Helper II

The result is the table in the post

Ahmedx
Super User
Super User

show what result you expect

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.