The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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.
Solved! Go to 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/
Proud to be a Super User!
@Anonymous,
If this code is in a calculated table, remove "EVALUATE".
If you are running this code in DAX Studio, remove "CMDB&Bundle =".
Proud to be a Super User!
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.
@Anonymous,
Since this is a calculated table, you don't need "EVALUATE". Delete line 2 and it should work.
Proud to be a Super User!
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.
@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
Proud to be a Super User!
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
Result
Additional reading:
https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/
Proud to be a Super User!
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)).
@Anonymous,
Since your table names contain spaces, you need to enclose the table names in single quotes.
Proud to be a Super User!
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
99 | |
94 | |
85 | |
70 | |
65 |
User | Count |
---|---|
244 | |
127 | |
119 | |
81 | |
78 |