Forum Discussion
Using TREATAS to join 2 tables
I am trying to combine two tables together that are the SQL equivalent of
SELECT s.StudentId,
s.[School Year],
s.School,
a.Date,
a.[Expected Amount],
a.[Present Amount]
FROM PowerBIActiveAndInactiveStudentsWithLastSchool_2Yrs s
INNER JOIN PowerBIStudentAttendanceByDay_2Yrs a ON s.StudentId = a.StudentId AND s.Key_Year = a.Key_Year AND s.School = a.School
WHERE s.IsActive = 1
I tried
NewTable =
VAR FilteredStudents =
FILTER(
'PowerBI ActiveAndInactiveStudentsWithLastSchool_2Yrs',
[IsActive] = 1
)
RETURN
SELECTCOLUMNS(
NATURALINNERJOIN(
FilteredStudents,
'PowerBI StudentAttendanceByDay_2Yrs'
),
"StudentId", 'PowerBI StudentAttendanceByDay_2Yrs'[StudentId],
"School Year", 'PowerBI StudentAttendanceByDay_2Yrs'[School Year],
"School", 'PowerBI StudentAttendanceByDay_2Yrs'[School],
"Date", 'PowerBI StudentAttendanceByDay_2Yrs'[Date],
"Expected Amount", 'PowerBI StudentAttendanceByDay_2Yrs'[Expected Amount],
"Present Amount", 'PowerBI StudentAttendanceByDay_2Yrs'[Present Amount]
)
but that generated the error "No common join columns detected. The join function NATURALINNERJOIN requires at least one common join column." I do have a many:many relationship between the two tables based on Student Id.
I read that I should use TREATAS to create virtual relationships, but I can't figure out how to make it work.
- Anonymous1 year ago
Hi Kimber,
Based on my tests,
TREATAScannot directly reference theFilteredStudentsvariable.Try the following workaround:
NewTable2 = VAR FilteredStudents = SELECTCOLUMNS( FILTER( 'PowerBI ActiveAndInactiveStudentsWithLastSchool_2Yrs', [IsActive] = 1 ), "StudentId", [StudentId], "Key_Year", [Key_Year], "School", [School] ) RETURN DISTINCT( SELECTCOLUMNS( FILTER( 'PowerBI StudentAttendanceByDay_2Yrs', 'PowerBI StudentAttendanceByDay_2Yrs'[StudentId] IN DISTINCT(SELECTCOLUMNS(FilteredStudents, "StudentId", [StudentId])) && 'PowerBI StudentAttendanceByDay_2Yrs'[Key_Year] IN DISTINCT(SELECTCOLUMNS(FilteredStudents, "Key_Year", [Key_Year])) && 'PowerBI StudentAttendanceByDay_2Yrs'[School] IN DISTINCT(SELECTCOLUMNS(FilteredStudents, "School", [School])) ), "StudentId", 'PowerBI StudentAttendanceByDay_2Yrs'[StudentId], "School Year", 'PowerBI StudentAttendanceByDay_2Yrs'[School Year], "School", 'PowerBI StudentAttendanceByDay_2Yrs'[School], "Date", 'PowerBI StudentAttendanceByDay_2Yrs'[Date], "Expected Amount", 'PowerBI StudentAttendanceByDay_2Yrs'[Expected Amount], "Present Amount", 'PowerBI StudentAttendanceByDay_2Yrs'[Present Amount] ))For more detailed information, please see the attachment.
1 Reply
- AnonymousNot applicable
Hi Kimber,
Based on my tests,
TREATAScannot directly reference theFilteredStudentsvariable.Try the following workaround:
NewTable2 = VAR FilteredStudents = SELECTCOLUMNS( FILTER( 'PowerBI ActiveAndInactiveStudentsWithLastSchool_2Yrs', [IsActive] = 1 ), "StudentId", [StudentId], "Key_Year", [Key_Year], "School", [School] ) RETURN DISTINCT( SELECTCOLUMNS( FILTER( 'PowerBI StudentAttendanceByDay_2Yrs', 'PowerBI StudentAttendanceByDay_2Yrs'[StudentId] IN DISTINCT(SELECTCOLUMNS(FilteredStudents, "StudentId", [StudentId])) && 'PowerBI StudentAttendanceByDay_2Yrs'[Key_Year] IN DISTINCT(SELECTCOLUMNS(FilteredStudents, "Key_Year", [Key_Year])) && 'PowerBI StudentAttendanceByDay_2Yrs'[School] IN DISTINCT(SELECTCOLUMNS(FilteredStudents, "School", [School])) ), "StudentId", 'PowerBI StudentAttendanceByDay_2Yrs'[StudentId], "School Year", 'PowerBI StudentAttendanceByDay_2Yrs'[School Year], "School", 'PowerBI StudentAttendanceByDay_2Yrs'[School], "Date", 'PowerBI StudentAttendanceByDay_2Yrs'[Date], "Expected Amount", 'PowerBI StudentAttendanceByDay_2Yrs'[Expected Amount], "Present Amount", 'PowerBI StudentAttendanceByDay_2Yrs'[Present Amount] ))For more detailed information, please see the attachment.