Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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...
Need Help
  • DataInsights's avatar
    DataInsights
    4 years ago

    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
    Result

     

    Additional reading:

     

    https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/ 

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi DataInsights 

After applying your suggestion, this is still giving me the error below

 

 

So I went back to the documentation for NATURALLEFTOUTERJOIN and tried a more basic approach:

 

A new joined table with only the two columns that will match for my join:

 

 

And although it seems to be closer to the correct syntax, now I am having an issue with the data type I'd say. Reading around, found a lot of suggestions for adding a '+0' at the end of the two columns, but that only seems to be working if the data type is a number, for this case is a string I'm not sure where to go.

DataInsights's avatar
DataInsights
Super User
4 years ago

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

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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...

    • DataInsights's avatar
      DataInsights
      Super User
      4 years ago

      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
    Result

       

      Additional reading:

       

      https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/