Forum Discussion
Join two tables
Im trying to left join the two tables below: Left table ('SN CMDB Prod DW') and Right table ('SN Bundle Prod DW') with a left outer join in order to keep all my records from the left and see whateve...
Anonymous,
Try this. The column data type is Text.
CMDB&Bundle = VAR A = SELECTCOLUMNS ( 'SN Inventory (Bundle Prod DW)', "Remove Last 5", 'SN Inventory (Bundle Prod DW)'[Remove Last 5] & "" ) VAR B = SELECTCOLUMNS ( 'SN Inventory (CMDB Prod DW)', "Remove Last 5", 'SN Inventory (CMDB Prod DW)'[Remove Last 5] & "" ) VAR Result = NATURALLEFTOUTERJOIN ( A, B ) RETURN ResultAdditional reading:
https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/
Anonymous,
This appears to be a lineage issue. NATURALLEFTOUTERJOIN requires both tables to have the same lineage. Try using TREATAS to change the lineage:
CMDB&Bundle =
VAR A =
SELECTCOLUMNS (
'SN Inventory (Bundle Prod DW)',
"Remove Last 5", 'SN Inventory (Bundle Prod DW)'[Remove Last 5]
)
VAR B =
TREATAS (
SELECTCOLUMNS (
'SN Inventory (CMDB Prod DW)',
"Remove Last 5", 'SN Inventory (CMDB Prod DW)'[Remove Last 5]
),
'SN Inventory (Bundle Prod DW)'[Remove Last 5]
)
VAR Result =
NATURALLEFTOUTERJOIN ( A, B )
RETURN
Result
that gave me a:
"No common join columns detected. The join function 'NATURALLEFTOUTERJOIN' requires at-least one common join column." but those are the columns with names in common that I can easily join with other tools...
Anonymous,
Try this. The column data type is Text.
CMDB&Bundle = VAR A = SELECTCOLUMNS ( 'SN Inventory (Bundle Prod DW)', "Remove Last 5", 'SN Inventory (Bundle Prod DW)'[Remove Last 5] & "" ) VAR B = SELECTCOLUMNS ( 'SN Inventory (CMDB Prod DW)', "Remove Last 5", 'SN Inventory (CMDB Prod DW)'[Remove Last 5] & "" ) VAR Result = NATURALLEFTOUTERJOIN ( A, B ) RETURN ResultAdditional reading:
https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/