Power BI星型架构下DAX函数失效及学生维度表问题咨询

Hey there, let's work through your Google Classroom star schema challenges—you’ve already put in a solid week of work, so let’s get this sorted for good!

First, let’s unpack why you’re seeing those broken results when adding StudentName and using that MarkPct formula:

• Empty student names happen because your gc_DimStudents includes all enrolled students, but gc_FactSubmissions only has records for students who submitted work. When you pull in StudentName, Power BI tries to match all students to submission data, leaving blanks for those who never submitted.
• The messed-up totals and RELATED() limitation stem from a mismatch in your model’s context: your current setup ties course enrollment directly to submission data, which isn’t the right way to separate "who enrolled" vs "who submitted work".

Let’s tackle your specific questions one by one:

1. Should I turn gc_DimCourses into a fact table, and create separate student dimension tables? #

Nope—gc_DimCourses should stay a dimension table. What you do need is a new fact table to track student-course enrollments (let’s call it gc_FactEnrollments). This table will have one row per student-course pair (with fields like StudentID, CourseID, and optionally an enrollment date).

• This fixes your enrollment count problem: instead of relying on gc_FactSubmissions to count students, you’ll use DISTINCTCOUNT(gc_FactEnrollments[StudentID]) which accurately counts all enrolled students, even those who never submitted work.
• You don’t need separate student dimension tables (like gc_DimCourseStudents or gc_DimSubmissionStudents)—that’s an anti-pattern in star schemas. Stick with a single gc_DimStudents table for all student-related data (submissions, enrollments, attendance, etc.) to keep data consistent and avoid duplication.

2. Do I need surrogate keys to link gc_FactSubmissions to new fact tables? #

Surrogate keys aren’t strictly required if your natural keys (like StudentID, CourseID) are stable, unique, and won’t change over time. However, adding surrogate keys (e.g., EnrollmentKey for gc_FactEnrollments) will make your schema more flexible if you ever need to integrate data from other systems where student/course IDs might differ. For now, you can start with composite natural keys (e.g., StudentID + CourseID for gc_FactEnrollments) and add surrogate keys later if needed.

3. Is multiple student dimension tables okay in a star schema? #

Absolutely not—star schemas rely on single, centralized dimension tables for each business entity (students, courses, etc.). Having multiple student tables will lead to data inconsistencies (e.g., different student names for the same ID across tables) and make your model harder to maintain. Keep one gc_DimStudents table, and link all student-related fact tables (submissions, enrollments, attendance, grades) to it.

If you need to get your current visualization working while you adjust the schema:

• Fix empty student names: Add a filter to your table visual to exclude students with no submission data (use gc_FactSubmissions[StudentID] IS NOT BLANK). Alternatively, use this DAX to handle blanks explicitly:

  StudentName = 
  IF(
      ISBLANK(RELATED(gc_DimStudents[StudentName])),
      "No Submission Record",
      RELATED(gc_DimStudents[StudentName])
  )
  

• Fix MarkPct totals: Replace your current formula with SUMX() to calculate percentages row-by-row, which avoids context errors in totals:

  MarkPct = 
  SUMX(
      gc_FactSubmissions,
      DIVIDE(
          gc_FactSubmissions[PointsEarned],
          RELATED(gc_DimCourseWork[MaxPoints]),
          0 // Returns 0 instead of blank if MaxPoints is 0
      )
  )
  

To support future data like grades, attendance, and discipline, build out your star schema like this:

Dimension Tables #

• gc_DimStudents: All student details (StudentID, StudentName, etc.)
• gc_DimCourses: All course details (CourseID, CourseName, etc.)
• gc_DimCourseWork: All assignment/task details (CourseWorkID, CourseWorkName, MaxPoints, etc.)
• gc_DimOwners: All teacher/owner details (OwnerID, OwnerName, etc.)
• gc_DimDates: A date dimension (critical for time-based analysis—add this if you don’t have it already!)

Fact Tables #

• gc_FactEnrollments: Tracks student-course enrollments (StudentID, CourseID, EnrollmentDate)
• gc_FactSubmissions: Your existing submission data (links to StudentID, CourseID, CourseWorkID, OwnerID, SubmissionDate)
• gc_FactGrades: Tracks formal course grades (StudentID, CourseID, GradeValue, GradeDate)
• gc_FactAttendance: Tracks attendance records (StudentID, CourseID, AttendanceStatus, AttendanceDate)
• gc_FactDiscipline: Tracks discipline incidents (StudentID, CourseID, IncidentType, IncidentDate)

Each fact table links to the relevant dimension tables via their ID fields. This setup is clean, scalable, and will make building reports/dashboards for all your future data needs straightforward.

内容的提问来源于stack exchange，提问作者bpnit