Natural Left Outer Join in DAX?

[AliceW]

Hello everyone,

This should be simple, but I can't find a solution, so I turn to you, kind strangers :0)

I need to merge two tables using NATURALLEFTOUTERJOIN. I need this in DAX instead of PowerQuery because, well, the dataset is too big and I get refresh errors.

I need to join, say, these two based on 'Opp ID'. And only keep the columns that appear in both tables once ('Close Date' and 'MCL')

Table 'Products'

Opp ID	Close Date	MCL	Parent Product	Amount
123	26-04-2022	Amplify	API Builder	50000
123	26-04-2022	Amplify	API Manager	20000
123	26-04-2022	Amplify	API Portal	10000

Table 'Splits'

Opp ID	Close Date	MCL	Split Owner	Percentage
123	26-04-2022	Amplify	John Smith	80%
123	26-04-2022	Amplify	Nadia Moran	20%

I tried this, but I got an error that 'an incompatible join column, 'Opp ID', was detected'.

Left Outer =

var SPLITTABLE =

SELECTCOLUMNS(Splits,"Opp ID",Splits[Opp ID],"Split Owner", Splits[Split Owner])

var PRODTABLE =

selectcolumns(Products,"Opp ID", Products[Opp ID],"Parent Product", Products[Parent Product])

return

NATURALLEFTOUTERJOIN(PRODTABLE,SPLITTABLE)

The result should look like this (did it in PowerQuery)

Can anyone help, please?

Thank you,

Alice

[PC2790]

There are some considerations while using the function.

"There is no sort order guarantee for the results.
Columns being joined must have the same data type and the same name in both tables.
The columns considered for the join are those of the expanded table, not just the base table: two tables can be joined through common columns in related tables.
The columns used in the join condition that correspond to physical columns of the data model must also have the same data lineage; two columns with the same name and different data lineage generate an error.
Two columns with the same data lineage must have also the same full column name, which includes both table name and column name; otherwise, they are not matched for the join.
Strict comparison semantics are used during the join. There is no type coercion; for example, 1 does not equal 1.0."

Refer this for the same

[AliceW]

I have the same data type for the column I wish to use as a joiner... Not sure about the meaning of 'data lineage'...

[PC2790 · Accepted solution]

Try this:

Left Outer = 
var SPLITTABLE =
SELECTCOLUMNS(Splits,"Opp ID",Splits[Opp ID]+0,"Split Owner", Splits[Split Owner])
var PRODTABLE =
selectcolumns(Products,"Opp ID", Products[Opp ID]+0,"Parent Product", Products[Parent Product])
return
NATURALLEFTOUTERJOIN(PRODTABLE,SPLITTABLE)

Outcome as below:

[udayravi]

Hi,

Suppose, Performed left outer join Table having one more column like Id.

The Id Column should be join with more than one table. is it possible ?

I believe it perform join with one table only.

[AliceW]

Thank you so much, PC! Not sure why that pesky +0 was needed to 'break the lineage', but it works!!