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

[Chris2016]

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 · Accepted solution]

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]

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

Best regards