Uso de TREATAS para unir 2 tablas

[Syndicate_Admin]

Estoy tratando de combinar dos tablas que son el equivalente SQL de

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

Lo intenté

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]
    )

pero eso generó el error "No se detectaron columnas de combinación comunes. La función de combinación NATURALINNERJOIN requiere al menos una columna de combinación común". Tengo una relación muchos:muchos entre las dos tablas basada en la identificación del estudiante.

Leí que debería usar TREATAS para crear relaciones virtuales, pero no sé cómo hacer que funcione.

[Syndicate_Admin]

Hola @Kimber,

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

Pruebe la siguiente solución alternativa:

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]
    ))

Para obtener información más detallada, consulte el archivo adjunto.