Forum Discussion
NaturalLeftOuterJoin doesn't joint tables
Hi, I'm joining two tables according https://www.sqlbi.com/articles/from-sql-to-dax-joining-tables/
but result table contain data just from table A. If I change order of tables in function to NaturalLeftOutreJoin( B, A) a get values just from table B.
Where I have error?
Thank you
This is my code.
UserUnitAssignmentsCombo =
var A =
SELECTCOLUMNS(
UserUnitAssignments,
"UnitId", UserUnitAssignments[UnitId]+0,
"HourlyRateCategory", UserUnitAssignments[HourlyRateCategory]&"",
"UserId", UserUnitAssignments[UserId]&"",
"UserUnitAssignmentId", UserUnitAssignments[UserUnitAssignmentId]+0,
"ValidFrom", DATEADD(UserUnitAssignments[ValidFrom].[Date],0,YEAR),
"ValidUntil", DATEADD(UserUnitAssignments[ValidUntil].[Date],0,YEAR),
"WorkingHoursPerWeek", UserUnitAssignments[WorkingHoursPerWeek]+0
)
var B =
SELECTCOLUMNS(
NonHumanResourceUnitAssignments,
"UnitId", NonHumanResourceUnitAssignments[UnitId]+0,
"HourlyRateCategory", NonHumanResourceUnitAssignments[HourlyRateCategory]&"",
"UserId", NonHumanResourceUnitAssignments[NonHumanResourceId]&"",
"UserUnitAssignmentId", NonHumanResourceUnitAssignments[NonHumanResourceUnitAssignmentId]+0,
"ValidFrom", DATEADD(NonHumanResourceUnitAssignments[ValidFrom].[Date],0,YEAR),
"ValidUntil", DATEADD(NonHumanResourceUnitAssignments[ValidUntil].[Date],0,YEAR),
"WorkingHoursPerWeek", NonHumanResourceUnitAssignments[WorkingHoursPerWeek]+0
)
var result1= NATURALLEFTOUTERJOIN(A, B)
return
result1
Hi Anonymous ,
As far as I know, NATURALLEFTOUTERJOIN(LeftTable, RightTable) function will return a table which includes only rows from RightTable for which the values in the common columns specified are also present in LeftTable. The table returned will have the common columns from the left table and the other columns from both the tables.
So in your code, A and B tables have same columns, NATURALLEFTOUTERJOIN(A, B) will only return all columns from left table (A).
Here I create a sample to show you how to use this function.
NonHumanResourceUnitAssignments:
UserUnitAssignments:
UserUnitAssignmentsCombo = var A = SELECTCOLUMNS( UserUnitAssignments, "UnitId", UserUnitAssignments[UnitId]&"", "UserId", UserUnitAssignments[UserId]&"", "WorkingHoursPerWeek", UserUnitAssignments[WorkingHoursPerWeek]+0 ) var B = SELECTCOLUMNS( NonHumanResourceUnitAssignments, "UnitId", NonHumanResourceUnitAssignments[UnitId]&"", "UserId", NonHumanResourceUnitAssignments[UserId]&"", "Value", NonHumanResourceUnitAssignments[Value]+0 ) var result1= NATURALLEFTOUTERJOIN(A, B) return result1Result is as below.
For more details you may refer to this solved post.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Hi Anonymous ,
As far as I know, NATURALLEFTOUTERJOIN(LeftTable, RightTable) function will return a table which includes only rows from RightTable for which the values in the common columns specified are also present in LeftTable. The table returned will have the common columns from the left table and the other columns from both the tables.
So in your code, A and B tables have same columns, NATURALLEFTOUTERJOIN(A, B) will only return all columns from left table (A).
Here I create a sample to show you how to use this function.
NonHumanResourceUnitAssignments:
UserUnitAssignments:
UserUnitAssignmentsCombo = var A = SELECTCOLUMNS( UserUnitAssignments, "UnitId", UserUnitAssignments[UnitId]&"", "UserId", UserUnitAssignments[UserId]&"", "WorkingHoursPerWeek", UserUnitAssignments[WorkingHoursPerWeek]+0 ) var B = SELECTCOLUMNS( NonHumanResourceUnitAssignments, "UnitId", NonHumanResourceUnitAssignments[UnitId]&"", "UserId", NonHumanResourceUnitAssignments[UserId]&"", "Value", NonHumanResourceUnitAssignments[Value]+0 ) var result1= NATURALLEFTOUTERJOIN(A, B) return result1Result is as below.
For more details you may refer to this solved post.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I found one, it is function Union. For more datail see https://radacad.com/combining-tables-in-power-bi-union-except-and-intersect-in-dax