Dynamic table with join

DAX DAX Calculations
1

Hi @Sam,
I need to create a table dynamically and add a new column coming from a table in the model (I’m using a mixed-mode model)

I have a table in a SQL DB that lists regions and subregions. I cannot have this table changed.
So I was thinking to create a table for the tuples I’m interested with ALL(‘Dim SubRegion’[RegionName], ‘Dim SubRegion’[SubRegion]), then add the sort columns using a join.
Unfortunately, it didn’t work since the columns RegionName in the two tables have a different lineage.
I also tried with a table in the model using a SUMMARIZE then adding columns with LOOKUP. It worked but it’s not very elegant.
How can I create such a table in a smarter way?

Thanks for the support

Roberto

image
image1047×400 13.6 KB

EVALUATE
all(‘Dim SubRegion’[RegionName], ‘Dim SubRegion’[SubRegion])

1 Like
2

@Roberto ,

I have some ideas for this I’d like to test. Can you please attach the file that you created the screenshot above from?

Thanks.

  • Brian
1 Like
3

Hi @BrianJ,
I’ve attached the excel with sample data and a power bi loading it.
I cannot make any ETL on the table on the DB, but I can have a local table to add a sort code to regions (users want to see regions in a specific order).
The Subregion table may change with no notice (subregions can be added/removed) while Regions are more stable, so I can maintain this local table without much effort.

Thanks for your help

Roberto

dynamic.pbix (17.9 KB)
dynamic.xlsx (20.5 KB)

1 Like
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
5

Hi @BrianJ,
it worked nicely.
I tried to use DATATABLE to create a temporary table but NATURALLEFTOUTERJOIN requires a base table. I think it makes sense since it is required in the model not only in the query.

Thanks a lot

Roberto

1 Like