SSAS Tabular Model - Analyze Students without Assigned Grades

Question

Hello,

Need help with an SSAS Tabular model. My requirements are to analyze students with grades and those without.

The model has the following tables.

• a Grades Fact table
• a Courses Bridge table and
• a Students Dimension table

Relationship: Fact has many-to-many relationship with the Courses, and Students can be enrolled in one-to-many courses.

Today I can analyze my data by Students with grades, but I also want to be able to analyze the data by Students without grades.

Thanks,

Francis

Answer 1

Hello Francis,

The issue you're experiencing is common when dealing with data analysis tasks. When we are analyzing "presence" data (in your case, students with grades), it's often easy to overlook "absence" data (students without grades). Fortunately, you can solve this problem by manipulating the data or creating calculated tables or columns.

Create a separate calculated table for all students. This can be achieved with a DAX formula such as:

AllStudents = DISTINCT('Students'[StudentID])

Create a calculated column in the new AllStudents table to check if each student has any grades. This can be done with the following DAX:

HasGrades = IF(

CALCULATE(

    COUNTROWS('Grades'),

    FILTER(

        'Grades',

        'Grades'[StudentID] = 'AllStudents'[StudentID]

    )

) > 0,

"Yes",

"No"

)

Now, you can use the new AllStudents table for your analysis. Students with 'HasGrades' = "Yes" have at least one grade, and students with 'HasGrades' = "No" do not have any grades.

Remember, this model requires that you have a 'StudentID' column in both the 'Students' and 'Grades' tables.

Answer 2

Thank you, Amira.

Got the approach implemented and it seems to be working, but for some reason the "No" counts are not being displayed in the UI. Any thoughts?