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/ 

DataInsights's avatar
DataInsights
Super User
4 years ago

Anonymous,

 

If this code is in a calculated table, remove "EVALUATE".

 

If you are running this code in DAX Studio, remove "CMDB&Bundle =".

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Thanks for the quick response and sorry for the late comment, this is my update:

 

I am going to the left menu and click on "Table" view and then create a new table, from there, I put the code I shared before.

 

The first part you suggested, "If this code is in a calculated table, remove "EVALUATE" only gives me a similar error (not sure if I should take your suggestion in such a literal way and just delete that line that reads -evaluate-

 

Regarding the second suggestion "If you are running this code in DAX Studio, remove "CMDB&Bundle =""

That part "CMDB&Bundle=" Is the name of the table, and that is how DAX starts every command for what I know so far, with the name of the table/column the instruction will be applied to.

 

 

  • DataInsights's avatar
    DataInsights
    Super User
    4 years ago

    Anonymous,

     

    Since this is a calculated table, you don't need "EVALUATE". Delete line 2 and it should work.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Unfortunately, by removing the line 2 "evaluate" I only get a new error message:

       

      The syntax for 'CMDB' is incorrect. (DAX(VAR A =SELECTCOLUMNS (SN CMDB Prod DW,"CUSTOMER_CMDB", SN CMDB Prod DW[CUSTOMER_CMDB]+0,"Remove Last 5_CMDB", SN CMDB Prod DW[Remove Last 5_CMDB],"Client Type_CMDB", SN CMDB Prod DW[Client Type_CMDB])VAR B =SELECTCOLUMNS (SN Bundle Prod DW,"Remove Last 5_Bundle", SN Bundle Prod DW[Remove Last 5_Bundle]+0,"CUSTOMER_Bundle", SN Bundle Prod DW[CUSTOMER_Bundle],"Client Type_Bundle", SN Bundle Prod DW[Client Type_Bundle])VAR Result =NATURALLEFTOUTERJOIN ( A, B )RETURNResult)).

       

      • DataInsights's avatar
        DataInsights
        Super User
        4 years ago

        Anonymous,

         

        Since your table names contain spaces, you need to enclose the table names in single quotes.

    • 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