Dynamic table with join

DAX DAX Calculations
4

@Roberto ,

I think this will work well for you. You can use DAX NATURALLEFTOUTERJOIN, but first you need to break the lineage with SELECTCOLUMNS.

Combined =

VAR vTable1 =
    SELECTCOLUMNS(
        'SubRegions',
        "Region", 'SubRegions'[RegionName] & "",
        "Subregion", 'SubRegions'[SubRegion]
    )
VAR vTable2 =
    SELECTCOLUMNS(
        'RegionsWithSortCode',
        "Region", 'RegionsWithSortCode'[RegionName] & "",
        "Sort", 'RegionsWithSortCode'[Sort]
    )
RETURN
    NATURALLEFTOUTERJOIN( vTable1, vTable2 )

image

I hope this is helpful. Full solution file attached below.

– Brian

PS - @sam.mckay typically leaves forum responses to the Expert Team, Power Users Group and members. Generally, you’ll get a better response if you just address your questions to All.

eDNA Forum -Combining Tables with Lineage Break Solution.pbix (18.1 KB)

3 Likes