在Power BI中使用DAX创建带数值的交叉矩阵表问题求助

现有Diagnosis表，其中# Category per Client measure用于统计每位客户的MH+SA类别数量，ClientID=23仅有一种类别"MH"，其余客户均有"MH"和"SA"两类。表数据如下：

需创建Power BI交叉矩阵表：X轴为MH类别类型，Y轴为SA类别类型，值为度量值，用于统计每个SA-MH类别对中# Category per Client=2的唯一客户数量。例如SA1-MH1对应3位客户，SA2-MH3对应0位客户等。

当前使用的DAX度量值：

# Dual Diagnosed Clients = CALCULATE(
                          DISTINCTCOUNT('Diagnosis'[ClientID]),
                            FILTER(
                                   'Diagnosis',
                                   'Diagnosis'[# Category per Client] = 2

                                  )
                                  )

但将该度量值放入矩阵后，显示的是# Category per Client=2的唯一客户总数，而非按各SA-MH类别对统计的数量。

问题根源 #

原度量值未考虑矩阵的上下文筛选（当前行的SA类别、当前列的MH类别），FILTER('Diagnosis', ...)遍历整个表，导致统计的是所有符合类别数=2的客户总数，而非对应SA-MH类别对的客户数。

适合入门的DAX度量值 #

# Dual Diagnosed Clients = 
-- 获取当前矩阵行对应的SA类别
VAR CurrentSA = SELECTEDVALUE('Diagnosis'[DiagnosDescription])
-- 获取当前矩阵列对应的MH类别
VAR CurrentMH = SELECTEDVALUE('Diagnosis'[DiagnosDescription])
RETURN
CALCULATE(
    DISTINCTCOUNT('Diagnosis'[ClientID]),
    FILTER(
        ALL('Diagnosis'),
        -- 筛选类别数为2的客户
        'Diagnosis'[# Category per Client measure] = 2 &&
        -- 验证该客户存在当前SA类别的记录
        CALCULATE(
            COUNTROWS('Diagnosis'),
            FILTER('Diagnosis', 'Diagnosis'[ClientID] = EARLIER('Diagnosis'[ClientID]) && 'Diagnosis'[DiagnosDescription] = CurrentSA)
        ) > 0 &&
        -- 验证该客户存在当前MH类别的记录
        CALCULATE(
            COUNTROWS('Diagnosis'),
            FILTER('Diagnosis', 'Diagnosis'[ClientID] = EARLIER('Diagnosis'[ClientID]) && 'Diagnosis'[DiagnosDescription] = CurrentMH)
        ) > 0
    )
)

更简洁的写法 #

如果对DAX有一定理解，也可以用集合交集的方式实现：

# Dual Diagnosed Clients = 
CALCULATE(
    DISTINCTCOUNT('Diagnosis'[ClientID]),
    'Diagnosis'[# Category per Client measure] = 2,
    -- 取当前SA类别客户集合与当前MH类别客户集合的交集
    INTERSECT(
        CALCULATETABLE(VALUES('Diagnosis'[ClientID]), ALLSELECTED('Diagnosis'[DiagnosDescription])),
        CALCULATETABLE(VALUES('Diagnosis'[ClientID]), 'Diagnosis'[DiagnosDescription] IN VALUES('Diagnosis'[DiagnosDescription]))
    )
)

效果说明 #

两种写法都能正确响应矩阵的上下文：

• 针对每个SA-MH类别对，只统计同时拥有这两个类别且# Category per Client measure=2的唯一客户数
• 无匹配的类别对（如SA2-MH3）会返回0，符合需求

内容的提问来源于stack exchange，提问作者Hell-1931