如何编写SQL或PowerBI度量值验证用户是否满足认证全部培训要求

表结构说明 #

• Certifications（认证要求表）：存储每个认证需要完成的所有培训条目，核心字段为CertificationID（认证唯一标识）、CertificationName（认证名称）、RequiredTrainingID（要求完成的培训ID）
• UserCertifications（用户完成记录表）：存储用户已完成的培训及关联认证信息，核心字段为UserID（用户唯一标识）、CertificationID（关联的认证ID）、CompletedTrainingID（用户已完成的培训ID）

实现方案 #

SQL实现 #

实现逻辑为：先统计每个认证要求的培训总数量，再统计对应用户在该认证下完成的匹配培训数量，二者相等则判定为完成全部要求。

SELECT 
    uc.UserID,
    c.CertificationName,
    CASE 
        WHEN COUNT(DISTINCT uc.CompletedTrainingID) = MAX(c.required_train_cnt) THEN '已完成全部要求培训'
        ELSE '未完成全部要求培训'
    END AS completion_status
FROM UserCertifications uc
INNER JOIN (
    -- 统计每个认证要求的培训总数
    SELECT 
        CertificationID,
        CertificationName,
        COUNT(DISTINCT RequiredTrainingID) AS required_train_cnt
    FROM Certifications
    GROUP BY CertificationID, CertificationName
) c ON uc.CertificationID = c.CertificationID
-- 过滤掉用户完成的不属于该认证要求的培训
WHERE EXISTS (
    SELECT 1 
    FROM Certifications c2
    WHERE c2.CertificationID = uc.CertificationID
    AND c2.RequiredTrainingID = uc.CompletedTrainingID
)
GROUP BY uc.UserID, c.CertificationName
-- 按需添加筛选条件，比如查询UserID=1的A认证完成情况
-- HAVING uc.UserID = 1 AND c.CertificationName = 'A认证'

PowerBI度量值实现 #

首先需在PowerBI模型中建立两表关联：Certifications[CertificationID] 与 UserCertifications[CertificationID] 建立一对多关系。
度量值DAX代码如下：

认证培训完成状态 = 
VAR 选中用户 = SELECTEDVALUE(UserCertifications[UserID])
VAR 选中认证 = SELECTEDVALUE(Certifications[CertificationID])
// 计算当前认证要求的培训总数量
VAR 要求培训总数 = CALCULATE(COUNTROWS(Certifications), ALLEXCEPT(Certifications, Certifications[CertificationID]))
// 计算当前用户在该认证下完成的符合要求的培训数量
VAR 已完成匹配数 = 
CALCULATE(
    COUNTROWS(UserCertifications),
    TREATAS(VALUES(Certifications[RequiredTrainingID]), UserCertifications[CompletedTrainingID]),
    ALLEXCEPT(UserCertifications, UserCertifications[UserID], UserCertifications[CertificationID])
)
RETURN
SWITCH(
    TRUE(),
    ISBLANK(选中用户) || ISBLANK(选中认证), "请选择对应查看的用户和认证",
    已完成匹配数 = 要求培训总数, "已完成全部要求培训",
    "未完成全部要求培训"
)

使用方式：将度量值放入表格/矩阵视觉对象，行维度添加UserID、CertificationName，即可批量查看所有用户对应认证的完成状态，也可添加筛选器单独查询指定用户和认证的结果。

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