DAX中RELATED()函数报错：Power BI英超赛事最大比分查询问题

报错原因 #

你定义的HomeTeam和AwayTeam变量是在度量的全局上下文里计算的，而非CONCATENATEX迭代BiggestGDRow的行上下文当中。RELATED函数需要当前行上下文存在与目标表的关联上下文，但外层全局上下文没有Matches的行上下文支撑，自然无法识别Clubs与当前上下文的关系，导致报错。

修正方案 #

使用LOOKUPVALUE函数替代RELATED，它无需依赖行上下文的关系，可直接通过ID匹配获取球队名称。以下是两种可行的修正代码：

方案一：在迭代过程中直接计算球队名称

HighestWin = 
VAR BiggestGD = MAXX(Matches, ABS(Matches[home_team_goals] - Matches[away_team_goals]))
VAR BiggestGDRow = FILTER(Matches, ABS(Matches[home_team_goals] - Matches[away_team_goals]) = BiggestGD)

VAR ResultList = CONCATENATEX(
    BiggestGDRow,
    LOOKUPVALUE(Clubs[club_name], Clubs[club_id], Matches[home_team_id]) 
    & " " & Matches[home_team_goals] & ":" & Matches[away_team_goals] & " " 
    & LOOKUPVALUE(Clubs[club_name], Clubs[club_id], Matches[away_team_id]),
    ", "
)

RETURN ResultList

方案二：提前扩展行表添加球队名称

HighestWin = 
VAR BiggestGD = MAXX(Matches, ABS(Matches[home_team_goals] - Matches[away_team_goals]))
VAR BiggestGDRow = 
    ADDCOLUMNS(
        FILTER(Matches, ABS(Matches[home_team_goals] - Matches[away_team_goals]) = BiggestGD),
        "HomeTeamName", LOOKUPVALUE(Clubs[club_name], Clubs[club_id], Matches[home_team_id]),
        "AwayTeamName", LOOKUPVALUE(Clubs[club_name], Clubs[club_id], Matches[away_team_id])
    )

VAR ResultList = CONCATENATEX(
    BiggestGDRow,
    [HomeTeamName] & " " & [home_team_goals] & ":" & [away_team_goals] & " " & [AwayTeamName],
    ", "
)

RETURN ResultList

补充说明 #

如果Clubs与Matches的两个关系中一个是活动关系、一个是非活动关系，也可以保留USERELATIONSHIP，但需要将其放到行上下文内。例如在CONCATENATEX中用CALCULATE(RELATED(...), USERELATIONSHIP(...))，但LOOKUPVALUE的写法更简洁且不依赖关系的活动状态，更适合当前场景。

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