Join two tables

[Anonymous]

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.

[DataInsights]

@Anonymous,

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

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

[Anonymous]

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]

@Anonymous,

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

[Anonymous]

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]

@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]

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 · Accepted solution]

@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]

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]

@Anonymous,

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