如何用Power Query/DAX统计各intake未选修其他课程的学生数

解决方案：Power Query 实现步骤 #

1. 导入数据：将原始数据导入Power Query，确认列名intake/class/student_id正确
2. 生成学生选课映射表：
   • 选择student_id和class列，点击「移除重复项」，得到每个学生已选的课程列表
3. 按intake分组学生：
   • 回到原始表，按intake分组，聚合方式选「所有行」，再展开分组结果，只保留student_id并去重，得到每个intake的唯一学生集合
4. 合并数据并统计：
   • 将intake学生集合与选课映射表按student_id左连接，添加三个条件列：
     • no_english：if [class] <> "English" or [class] is null then 1 else 0
     • no_science：if [class] <> "Science" or [class] is null then 1 else 0
     • no_biology：if [class] <> "Biology" or [class] is null then 1 else 0
   • 再次按intake分组，对三个条件列求和，得到最终统计结果

解决方案：DAX 实现方式 #

方法1：创建计算表

IntakeCourseStats = 
VAR AllIntakes = VALUES('原始表'[intake])
VAR StudentCourseMap = 
    ADDCOLUMNS(
        VALUES('原始表'[student_id]),
        "CoursesTaken", CALCULATETABLE(VALUES('原始表'[class]), ALLEXCEPT('原始表', '原始表'[student_id]))
    )
VAR FinalStats = 
    GENERATE(
        AllIntakes,
        VAR CurrentStudents = CALCULATETABLE(VALUES('原始表'[student_id]), ALLEXCEPT('原始表', '原始表'[intake]))
        VAR NoEnglish = COUNTROWS(FILTER(CurrentStudents, NOT "English" IN LOOKUPVALUE(StudentCourseMap[CoursesTaken], StudentCourseMap[student_id], [student_id])))
        VAR NoScience = COUNTROWS(FILTER(CurrentStudents, NOT "Science" IN LOOKUPVALUE(StudentCourseMap[CoursesTaken], StudentCourseMap[student_id], [student_id])))
        VAR NoBiology = COUNTROWS(FILTER(CurrentStudents, NOT "Biology" IN LOOKUPVALUE(StudentCourseMap[CoursesTaken], StudentCourseMap[student_id], [student_id])))
        RETURN
            ROW(
                "no_english", IF(ISBLANK(NoEnglish), COUNTROWS(CurrentStudents), NoEnglish),
                "no_science", IF(ISBLANK(NoScience), COUNTROWS(CurrentStudents), NoScience),
                "no_biology", IF(ISBLANK(NoBiology), COUNTROWS(CurrentStudents), NoBiology)
            )
    )
RETURN
    SELECTCOLUMNS(FinalStats, "intake", [intake], "no_english", [no_english], "no_science", [no_science], "no_biology", [no_biology])

方法2：创建度量值配合矩阵可视化

分别创建三个度量值：

no_english = 
VAR CurrentIntakeStudents = CALCULATETABLE(VALUES('原始表'[student_id]), ALLEXCEPT('原始表', '原始表'[intake]))
VAR StudentsWithEnglish = CALCULATETABLE(VALUES('原始表'[student_id]), '原始表'[class] = "English")
RETURN COUNTROWS(EXCEPT(CurrentIntakeStudents, StudentsWithEnglish))

no_science = 
VAR CurrentIntakeStudents = CALCULATETABLE(VALUES('原始表'[student_id]), ALLEXCEPT('原始表', '原始表'[intake]))
VAR StudentsWithScience = CALCULATETABLE(VALUES('原始表'[student_id]), '原始表'[class] = "Science")
RETURN COUNTROWS(EXCEPT(CurrentIntakeStudents, StudentsWithScience))

no_biology = 
VAR CurrentIntakeStudents = CALCULATETABLE(VALUES('原始表'[student_id]), ALLEXCEPT('原始表', '原始表'[intake]))
VAR StudentsWithBiology = CALCULATETABLE(VALUES('原始表'[student_id]), '原始表'[class] = "Biology")
RETURN COUNTROWS(EXCEPT(CurrentIntakeStudents, StudentsWithBiology))

创建矩阵可视化，行字段选intake，值字段选这三个度量值即可得到预期结果。

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