从叠加两层CALCULATE，发现的一些DAX基础知识中的细节

本文链接：https://blog.csdn.net/weixin_38775843/article/details/128961896

文章讲述了在DAX中使用嵌套CALCULATE函数计算预算台数时遇到的问题，由于筛选上下文的转换导致总计计算错误。解决方案是通过外层CALCULATE转换行上下文或使用FILTER与EARLIER函数恢复筛选器。强调了深入理解CALCULATE的行为、行上下文和筛选上下文对灵活运用DAX的重要性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一条旧笔记

翻到了一条自己以前记的笔记。大概内容是在某个情况下，一段 DAX 里嵌入了

CALCULATE(
	CALCULATE(...)
	//这里没有参数2
)

跟嵌入 CALCULATE(...) 的计算结果会不一样。当时，还觉得很有趣。现在再看，只因没学扎实。
当时的度量值：

预算件数2022 =   
SUMX(  
    DISTINCT('B015平台2'[平台2]),  
    var _price=  
        CALCULATE(  
            CALCULATE(  
                [单件收入],  
                ALLSELECTED(),  
                ALL('B001日期表'),  
                'B001日期表'[年]=2021,  
                VALUES('B015平台2'[平台2])  
            )  
        )
    return  
        SUMX(  
            DISTINCT('B001日期表'[年月]),  
            var _amount=[预算店铺收入]  
            return  
                DIVIDE(_amount,_price)  
        )  
)

问题相关背景介绍

我们要计算的是：为了完成预算的收入，各店铺在明年各月需要卖出多少件商品。
我们已知：各店铺明年各月的预算收入。还有今年的销售订单明细。
所有店铺可以分为 4 个平台，平台 A、平台 B、平台 C 与平台 D。每个平台各有若干店铺。
已知：同一平台的不同店铺，在不同的月份，卖出的商品，它们的单件平均价格都差不多，只有极小的不同。而平台不同，差异就会较大。
因此，我们要计算某个店铺在明年某月的预算台数，只要使用 [店铺的预算收入]/[该平台去年整年的平均价格]。之所以使用一个平台整年销售的平均价格，而不使用该店的，或使用去年同期月份的，是为防止有些店铺月份的销售数据缺失或异常，造成错误。

使用脱敏的模拟数据复现这个问题

一份 2023 年各月各店铺的收入预算表格

Pasted image 20230209193149|800

一份 2022 年的订单明细表

Pasted image 20230209193317|400

查看一下 2022 年各月各店的单件均价，可以看出只有在不同平台间才有明显的差异。

Pasted image 20230209193616|800

初步解决方法

建立模型

Pasted image 20230209204953|600

写入度量值 预算收入 = SUM('预算收入'[预算收入]) 和 单台均价 = DIVIDE(SUM('订单'[金额]),SUM('订单'[数量]))

先尝试写一个计算预算台数的度量值试下，

预算台数1 = 
SUMX(
    VALUES('店铺'[平台]),
    var price=
            CALCULATE(
                [单台均价],
                VALUES('店铺'[平台]),
                ALL('店铺'),
                ALL('日期表'),
                '日期表'[年序号]=2022
            )
    return DIVIDE([预算收入],price)
)

Pasted image 20230209211016

看看台数和预算单价，感觉好像是对的。但其实，总计中的数据现在是错误的。

初步解决方法中存在的问题

我们计算台数是分平台分店铺用预算收入除以该平台的平均价格计算的，与使用一个总的预算收入除以总的平均价格，必然是不同的。我们写一个计算后者的度量值，看看结果。

测试值 = DIVIDE([预算收入],CALCULATE([单台均价],ALL(),'日期表'[年序号]=2022))

Pasted image 20230209212216|200
这个数字正好是上面总计中总的台数。可知，在[预算台数 1]总计行的计算中，这个度量值并没有正确地为各店铺使用上对应平台去年的平均价格，而是使用了一个总的去年的平均价格。

分析原因

原因只能是度量值[预算台数 1]中里面的那个 VALUES('店铺'[平台]) 并没有正确起到作用。就是在 ALL('店铺') 移除了店铺表上的所有筛选器后，它来恢复 '店铺'[平台] 列筛选器的作用。

CALCULATE 函数会将外部的行上下文转换为 CALCULATE 内的是选器上下文，只要认真学习了 DAX 必然都会知道这一点。只是，有一个细节我当时没有掌握到，就是：它转换的筛选上下文是给到该CALCULATE 的第一参数的，第二参数及其他参数不会受到这个筛师上下文的影响。

为什么计算各店铺的预算台数时是正确的？

在计算各店铺或各平台的预算台数时，因为只有一个平台，所以外部的 VALUES('店铺'[平台]) 会产生一个 '店铺'[平台] 列上的筛选上下文，给到 CALCULATE。这个筛选上下文能影响 CALCULATE 内的所有参数。所以，内部的 VALUES('店铺'[平台]) 能正确的在 ALL('店铺') 移除了店铺表上的所有筛选器后，恢复 '店铺'[平台] 列上的筛选器。

为什么在计算总计的时候就不对了？

因为在计算总计时，外部的 VALUES('店铺'[平台]) 产生的筛选器是整列都包含，相当于没有这个筛选器。而 SUMX(VALUES ('店铺'[平台]),... 产生的是行上下文，虽然经过 CALCULATE 会转化为筛选上下文，但是转化来的筛选上下文只影响到这个 CALCULATE 的参数 1。内部的 VALUES('店铺'[平台]) 受不到任何筛选上下文的影响，通过它恢复出来的筛选上文，仍然是 '店铺'[平台] 列上的所有值。所以，只要是在总计行的计算中，price 的计算结果，必然是 2022 年整年所有平台的总的平均价格。

治标的方法

既然内部的 VALUES('店铺'[平台]) 受不到筛选的原因是 CALCULATE 转换出来的筛选上下文只能影响该CALCULATE 的参数 1，那我们再外层再套一个 CALCULATE，提前把外部的 VALUES('店铺'[平台]) 产生的行上下文转成筛选上下文，再传给内部的 CALCULATE。修改度量值：

预算台数2 = 
SUMX(
    VALUES('店铺'[平台]),
    var price=
        CALCULATE(
            CALCULATE(
                [单台均价],
                VALUES('店铺'[平台]),
                ALL('店铺'),
                ALL('日期表'),
                '日期表'[年序号]=2022
            )
        )
    return DIVIDE([预算收入],price)
)

Pasted image 20230209221000

可以看到不同的结果了。将表中的数据导出成 CSV , 检查各月各店铺的预算件数之和，确实是 20434.36。证明这个修改可以纠正上述错误。

治本的方法

我当时发现用连套两层 CALCULATE 可以解决问题，其实并不是什么有价值的学习经验。而限于自己对 DAX 的理解不深，和思维方面钻了牛角尖所致。要在要在内部几个 ALL 函数移除了筛选器后，把 '店铺'[平台] 的筛选器恢复出来，不一定要用 VALUES 来保留原有的筛选器。也可以利用 SUMX(VALUES ('店铺'[平台]),... 产生的是行上下文。用 FILTER('店铺','店铺'[平台]=EARLIER('店铺'[平台])) 代替内部的 VALUES('店铺'[平台])。完整的度量值：

预算台数3 = 
SUMX(
    VALUES('店铺'[平台]),
    var price=
        CALCULATE(
            [单台均价],
            FILTER('店铺','店铺'[平台]=EARLIER('店铺'[平台])),
            ALL('店铺'),
            ALL('日期表'),
            '日期表'[年序号]=2022
        )
    return DIVIDE([预算收入],price)
)