DAX度量优化：实现Flexteam工时修正值的正确行级展示

问题背景 #

需要计算各团队的[Hours worked for team]，规则为：

• 非Flexteam团队：修正值为自身的[Flexteam hours worked for team]，最终工时=合同工时+该修正值
• Flexteam团队：修正值为所有非Flexteam团队的[Flexteam hours worked for team]总和的负值，最终工时=合同工时+该修正值

正确DAX度量实现 #

1. 定义修正值度量 #

Correction flexteam = 
VAR CurrentTeam = SELECTEDVALUE(Team[Team])
-- 计算所有非Flexteam团队的Flexteam工时总和
VAR TotalFlexHoursForExternalTeams = 
    CALCULATE(
        [Flexteam hours worked for team],
        ALL(Team),
        Team[Team] <> "Flexteam"
    )
RETURN
    -- 根据当前团队判断返回对应修正值
    IF(
        CurrentTeam = "Flexteam",
        -TotalFlexHoursForExternalTeams,
        [Flexteam hours worked for team]
    )

2. 定义最终工时度量 #

Hours worked for team = 
[Contract hours] + [Correction flexteam]

原度量失效原因 #

原嵌套CALCULATE写法中，内层ALL(Team)会完全清除Team表的筛选上下文，导致无论当前行是哪个团队，都会计算所有非Flexteam的工时总和并取负。外层的FILTER(Team, Team[Team]="Flexteam")无法限制结果仅显示在Flexteam行，因为内层的ALL已经覆盖了筛选逻辑，最终所有行都会返回-6。

使用SELECTEDVALUE(Team[Team])可精准获取当前行的团队名称，结合IF条件判断，能实现不同团队的差异化计算逻辑，得到预期结果。

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