Combine Multiple Summary Tables into One

[aallman]

Hello!

I currently have 3 queries pulling from different sources. I have summarized each of these into summary tables that contain the ID column. All I need to do is get a comprehensive list of IDs from all 3 tables joined into one. I have looked into Union, NaturalInnerJoin, and CrossJoin but those all seem to be some variation of combining only rows that are found in all tables. I know that some of my IDs will be in all 3 tables but some will only appear in one. How can I get a final master list of IDs into one table?

Thanks in advance!

[aallman]

thank you this worked! I was not doing distinct and I think that was messing it up.

[parry2k · Accepted solution]

@aallman assuming your 3 tables called table 1, table 2 and table 3

you can use following DAX to create a new tables:

ID Table = 
DISTINCT (
   UNION ( 
       SELECTCOLUMNS ( Table1, "Id", Table1[Id] ),
       SELECTCOLUMNS ( Table2, "Id", Table2[Id] ),
       SELECTCOLUMNS ( Table3, "Id", Table3[Id] )
   )
)

[SachinNandanwar]

I think he mentioned that he has tried UNION and it didnt work for him.

[parry2k]

@SachinNandanwar understood but not sure how he used it, without knowing hard to tell. The expression I gave will work 100%.

[SachinNandanwar]

Please provide some sample data.