如何在单个度量值中实现动态关联值求和（禁用计算列等）

原始数据表 #

director    actor   date_added  show_id
x           a       2015        Meow
x           b       2015        Meow
x           a       2020        Woof
x           c       2020        Woof
y           d       1999        Moo
y           e       2002        CaCaw
z           f       2018        Bark

计算限制与要求 #

• 禁用计算列、模型级自定义表，不得修改原始数据或查询
• 需完成以下计算：

1. 计算导演存续时长（longevity）：公式为 MAX(date_added) - MIN(date_added) + 1，结果如下：

director    longevity
x           6
y           4
z           1

2. 统计导演-演员组合的影片数量：结果如下：

director    actor   # of movies
x           a       2
x           b       1
x           c       1
y           d       1
y           e       1
z           f       1

3. 关联上述结果：得到包含导演、演员、影片数、对应导演存续时长的关联表
4. 最终计算平均值：公式为 SUM(movies) / SUM(longevity)，示例结果为 7/27≈0.259

当前问题 #

现有实现逻辑无法完成“为每个影片数量值匹配对应导演的存续时长，求和后计算平均值”的需求，且数据扩容后会失效。

解决方案 #

使用以下DAX度量值，无需创建自定义表或计算列，直接在上下文完成所有计算：

AverageRatio = 
VAR ValidRows = FILTER('table', NOT(ISBLANK([director])) && NOT(ISBLANK([actor])) && NOT(ISBLANK([show_id])))
RETURN
DIVIDE(
    -- 分子：所有有效影片的总数
    CALCULATE(COUNT('table'[show_id]), ValidRows),
    -- 分母：每个导演-演员组合对应的导演存续时长之和
    SUMX(
        SUMMARIZE(ValidRows, 'table'[director], 'table'[actor]),
        CALCULATE(MAX('table'[date_added]) - MIN('table'[date_added]) + 1, ALLEXCEPT('table', 'table'[director]))
    )
)

逻辑说明 #

1. ValidRows 过滤掉存在空值的无效行
2. 分子直接计算有效行的show_id总数，即所有影片数量之和
3. 分母通过SUMX迭代每个导演-演员组合，利用ALLEXCEPT保留当前导演的上下文，计算该导演的存续时长，最终累加所有组合对应的时长值
4. 使用DIVIDE避免除零错误

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