Power BI计算周均Player Load Z值时的循环依赖问题求助

问题根源 #

你创建的Z-Score计算列直接引用了度量值Avg PL per Week，而该度量值又依赖WeekStart列，这种跨对象的上下文引用会触发循环依赖。计算列是逐行计算逻辑，度量值则依赖筛选上下文，两者混用很容易出现冲突。

替代方案 #

方案1：用计算表先汇总每周数据，再关联计算Z值 #

1. 先创建计算表，汇总每个运动员每周的平均Player Load：

WeeklyPlayerLoad = 
SUMMARIZE(
    'Catapult',
    'Catapult'[Player Name],
    'Catapult'[WeekStart],
    "WeeklyAvgPL", AVERAGE('Catapult'[Total Player Load])
)

2. 在这个计算表里直接添加Z值计算列：

Z-Score = 
VAR _athleteAvg = CALCULATE(
    AVERAGE(WeeklyPlayerLoad[WeeklyAvgPL]),
    ALLEXCEPT(WeeklyPlayerLoad, WeeklyPlayerLoad[Player Name])
)
VAR _athleteSD = CALCULATE(
    STDEVX.P(WeeklyPlayerLoad, WeeklyPlayerLoad[WeeklyAvgPL]),
    ALLEXCEPT(WeeklyPlayerLoad, WeeklyPlayerLoad[Player Name])
)
RETURN
DIVIDE(WeeklyPlayerLoad[WeeklyAvgPL] - _athleteAvg, _athleteSD)

3. 把原Catapult表和WeeklyPlayerLoad表通过Player Name、WeekStart建立关系，之后就能在原表中关联使用这个Z值了。

方案2：将Z值改为度量值，避开计算列的循环依赖 #

如果不需要把Z值作为固定列存储，可以直接做度量值：

Z-Score Measure = 
VAR _currentWeekAvg = [Avg PL per Week]
VAR _athleteAllWeeksAvg = CALCULATE(
    [Avg PL per Week],
    ALLEXCEPT('Catapult', 'Catapult'[Player Name])
)
VAR _athleteAllWeeksSD = CALCULATE(
    STDEVX.P(VALUES('Catapult'[WeekStart]), [Avg PL per Week]),
    ALLEXCEPT('Catapult', 'Catapult'[Player Name])
)
RETURN
DIVIDE(_currentWeekAvg - _athleteAllWeeksAvg, _athleteAllWeeksSD)

这个度量值会根据当前筛选上下文（比如选中的运动员、周）动态计算Z值，不会触发循环依赖。

方案3：调整计算列逻辑，直接基于原始数据计算 #

放弃用Avg PL per Week度量值，在计算列里直接完成所有计算：

Z-Score Column = 
VAR _player = 'Catapult'[Player Name]
VAR _week = 'Catapult'[WeekStart]
-- 计算当前周的平均Player Load
VAR _weekAvg = CALCULATE(
    AVERAGE('Catapult'[Total Player Load]),
    FILTER('Catapult', 'Catapult'[Player Name] = _player && 'Catapult'[WeekStart] = _week)
)
-- 计算该运动员所有周的平均Player Load
VAR _allWeeksAvg = CALCULATE(
    AVERAGE('Catapult'[Total Player Load]),
    ALLEXCEPT('Catapult', 'Catapult'[Player Name])
)
-- 计算该运动员所有周的标准差
VAR _allWeeksSD = CALCULATE(
    STDEVX.P('Catapult', 'Catapult'[Total Player Load]),
    ALLEXCEPT('Catapult', 'Catapult'[Player Name])
)
RETURN
DIVIDE(_weekAvg - _allWeeksAvg, _allWeeksSD)

注意：这种方式计算列的性能可能较差，尤其是数据集大的时候，优先选前两种方案。

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