Create a calculated table from two or more existing tables

Hi Anonymous,

I agree with Anonymous, this is easier to accomplish in Power Query. If you don't want to go that route, I answered a similar question just an hour or so ago. I am not completely satisfied that I arrived at the most elegant way to accomplish this but in case you want to have a look, check out my response at the end of this thread.

http://community.powerbi.com/t5/Desktop/Connecting-Two-Tables-w-Lookup/td-p/276284/page/2

Actually I found that what I was missing was the UNION function. Creating this calculated table gave me just what I needed:

Serial Numbers = DISTINCT(
    UNION(
        SUMMARIZE(
            'Dataset 1',
            'Dataset 1'[Serial #],
            ),
        SUMMARIZE(
            'Dataset 2',
            'Dataset 2'[Serial #],
            )
        )
    )

Then I was able to join this calculated table to each of the original tables and pull in location, using IFERROR to handle cases with more than one match:

Dateset 1 Location = IFERROR(
    LOOKUPVALUE(
        'Dataset 1'[Location],
        'Dataset 1'[Serial #],
        'Serial Numbers'[Serial #]
        ),
    "Multiple"
    )