Forum Discussion

aallman's avatar
aallman
Helper II
1 year ago
Solved

Combine Multiple Summary Tables into One

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!

Need Help
  • parry2k's avatar
    parry2k
    1 year ago

    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] )
   )
)

5 Replies

  • parry2k's avatar
    parry2k
    Super User
    1 year ago

    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's avatar
      SachinNandanwar
      Impactful Individual
      1 year ago

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

      • parry2k's avatar
        parry2k
        Super User
        1 year ago

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

  • aallman's avatar
    aallman
    Helper II
    1 year ago

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