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 whatever matches from the right.

 

This is the code I created based on the documentation available:

 

CMDB&Bundle =
EVALUATE
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 )
RETURN
Result

 

Unfortunately, I keep getting the error:

 

'The syntax for 'EVALUATE' is incorrect. (DAX(EVALUATEVAR 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 )RETURN Result)).'

 

Any ideas? Thank you.

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/ 

9 Replies

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