Forum Discussion

Kimber's avatar
Kimber
Helper III
1 year ago
Solved

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.

 

  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable

    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.