如何在Power BI中使用DAX解决159个测试列的相关性计算问题？

1 第一步：调整数据结构适配动态计算 #

你当前159个测试项为独立列的宽表结构不适合动态计算多变量相关系数，优先在Power Query中做逆透视转换：

• 选中PersonID列，右键选择「逆透视其他列」
• 转换后得到三列结构：PersonID、测试项名称（原列名）、测试得分（原列值）
  转换后无需修改模型即可支持任意两个测试项的相关系数计算，不需要硬编码159个列的逻辑。

2 第二步：修正DAX度量逻辑 #

你原有代码的核心问题是协方差计算中AVG1和AVG2逻辑完全一致，本质是在计算单变量的方差，而非两个变量的协方差，因此结果不符合预期。
首先创建两个独立的「测试项名称」辅助表（和主表无关联），分别作为X变量、Y变量的切片器选项，再编写如下度量：

协方差度量 #

Covarianza = 
VAR X_Selected = SELECTEDVALUE('测试项表X'[测试项名称])
VAR Y_Selected = SELECTEDVALUE('测试项表Y'[测试项名称])
// 筛选同时有X、Y两项测试成绩的用户
VAR Valid_Pairs = 
    FILTER(
        VALUES('public SabanaDatos'[PersonID]),
        CALCULATE(COUNT('public SabanaDatos'[测试得分]), 'public SabanaDatos'[测试项名称] = X_Selected) > 0
        && CALCULATE(COUNT('public SabanaDatos'[测试得分]), 'public SabanaDatos'[测试项名称] = Y_Selected) > 0
    )
VAR Pair_Count = COUNTROWS(Valid_Pairs)
VAR AVG_X = CALCULATE(AVERAGE('public SabanaDatos'[测试得分]), 'public SabanaDatos'[测试项名称] = X_Selected, ALLSELECTED('public SabanaDatos'[测试项名称]))
VAR AVG_Y = CALCULATE(AVERAGE('public SabanaDatos'[测试得分]), 'public SabanaDatos'[测试项名称] = Y_Selected, ALLSELECTED('public SabanaDatos'[测试项名称]))
RETURN
IF(
    Pair_Count >= 2, // 有效配对少于2组时返回空，避免无效值
    SUMX(
        Valid_Pairs,
        (CALCULATE(SUM('public SabanaDatos'[测试得分]), 'public SabanaDatos'[测试项名称] = X_Selected) - AVG_X)
        * (CALCULATE(SUM('public SabanaDatos'[测试得分]), 'public SabanaDatos'[测试项名称] = Y_Selected) - AVG_Y)
    ) / Pair_Count,
    BLANK()
)

皮尔逊相关系数量度 #

Coef_Corr = 
VAR X_Selected = SELECTEDVALUE('测试项表X'[测试项名称])
VAR Y_Selected = SELECTEDVALUE('测试项表Y'[测试项名称])
VAR STD_X = CALCULATE(STDEV.P('public SabanaDatos'[测试得分]), 'public SabanaDatos'[测试项名称] = X_Selected, ALLSELECTED('public SabanaDatos'[测试项名称]))
VAR STD_Y = CALCULATE(STDEV.P('public SabanaDatos'[测试得分]), 'public SabanaDatos'[测试项名称] = Y_Selected, ALLSELECTED('public SabanaDatos'[测试项名称]))
RETURN
DIVIDE([Covarianza], STD_X * STD_Y, BLANK())

3 第三步：生成全量相关系数矩阵 #

如果需要一次性展示159个测试项的两两相关系数：

• 矩阵行维度选测试项表X的测试项名称
• 矩阵列维度选测试项表Y的测试项名称
• 矩阵值放Coef_Corr度量
  筛选PersonID时会自动重新计算所有相关系数，不会出现R corrplot因数据量不足无法渲染的问题，还可以直接对相关系数做条件格式高亮。

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