Using TREATAS to join 2 tables

[Kimber]

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.

[v-yajiewan-msft · Accepted solution]

Hi @Kimber, 

Based on my tests, TREATAS cannot directly reference the FilteredStudents variable.

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.