Forum Discussion

Chris2016's avatar
Chris2016
Icon for Resolver I rankResolver I
3 years ago
Solved

Calculated table using DISTINCT UNION to get several columns from other tables

Hi,

I have a calculated table that is aggregating the (distinct) Name column from other tables. How do I tweak the formula to get other columns from those tables as well (such as Address, Location of the "Name", etc)?

Thanks a lot for any help!

New table= 
DISTINCT (
    UNION (
        VALUES ( 'Table1'[Name] ),
        VALUES ( 'Table2'[Name] ),
        VALUES ( 'Table3'[Name] ),
        VALUES ( 'Table4'[Name] ),
        VALUES ( 'Table5'[Name] ),
        VALUES ( 'Table6'[Name] ),
        VALUES ( 'Table7'[Name] )
    )
)
  • barritown's avatar
    barritown
    3 years ago

    Hi Chris2016, 

    If Address and Location are columns which are present in all the tables (Table1 ... Table7), you could transform your [DAX] query into something like this:

    New table= 
DISTINCT (
    UNION (
        SELECTCOLUMNS ( Table1, "Name, "[Name], "Address", [Address], "Location", [Location] ),
        SELECTCOLUMNS ( Table2, "Name, "[Name], "Address", [Address], "Location", [Location] ),
        SELECTCOLUMNS ( Table3, "Name, "[Name], "Address", [Address], "Location", [Location] ),
        SELECTCOLUMNS ( Table4, "Name, "[Name], "Address", [Address], "Location", [Location] ),
        SELECTCOLUMNS ( Table5, "Name, "[Name], "Address", [Address], "Location", [Location] ),
        SELECTCOLUMNS ( Table6, "Name, "[Name], "Address", [Address], "Location", [Location] ),
        SELECTCOLUMNS ( Table7, "Name, "[Name], "Address", [Address], "Location", [Location] )
    )
)

    Best Regards,

    Alexander

    My YouTube vlog in English

    My YouTube vlog in Russian

2 Replies

  • barritown's avatar
    barritown
    Icon for Solution Sage rankSolution Sage
    3 years ago

    Hi Chris2016, 

    If Address and Location are columns which are present in all the tables (Table1 ... Table7), you could transform your [DAX] query into something like this:

    New table= 
DISTINCT (
    UNION (
        SELECTCOLUMNS ( Table1, "Name, "[Name], "Address", [Address], "Location", [Location] ),
        SELECTCOLUMNS ( Table2, "Name, "[Name], "Address", [Address], "Location", [Location] ),
        SELECTCOLUMNS ( Table3, "Name, "[Name], "Address", [Address], "Location", [Location] ),
        SELECTCOLUMNS ( Table4, "Name, "[Name], "Address", [Address], "Location", [Location] ),
        SELECTCOLUMNS ( Table5, "Name, "[Name], "Address", [Address], "Location", [Location] ),
        SELECTCOLUMNS ( Table6, "Name, "[Name], "Address", [Address], "Location", [Location] ),
        SELECTCOLUMNS ( Table7, "Name, "[Name], "Address", [Address], "Location", [Location] )
    )
)

    Best Regards,

    Alexander

    My YouTube vlog in English

    My YouTube vlog in Russian

    • Chris2016's avatar
      Chris2016
      Icon for Resolver I rankResolver I
      3 years ago

      Yes, the other columns are in the same tables. Thanks a lot for the help, that works!

      Best regards