Forum Discussion
Power ParticipantNatural Left Outer Join in DAX?
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'.
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:
5 Replies
PC2790Community Champion
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
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: