CALCULATE 是至今为止 DAX 语言中最重要、最有用和最复杂的函数
引言
在本章中,通过深入介绍 CALCULATE 函数,我们将继续探索 DAX 语言的力量。实际上,同样的内容对于 CALCULATETABLE 也是适用的,只不过它返回一张表而不是标量值。为了简单起见,我们只在示例中使用 CALCULATE,但请记住 CALCULATETABLE 具有相同的行为。
专门用一整章来介绍这一个函数看似奇怪却很有必要,原因在于该函数的强大功能和副作用。CALCULATE 是至今为止 DAX 语言中最重要、最有用和最复杂的函数。在现实中该函数很简单:CALCULATE 仅执行一些特定类型的任务,但由于其可应用的场景数量之多以及公式编写之复杂,使得为其单独成章非常必要。
本章的内容比较复杂。如果是首次阅读,强烈建议你先阅读一遍以获得对 CALCULATE 的整体感觉,然后继续本书的其余章节。后期,一旦你对某个公式感到困惑,这时再回过头来从头开始阅读本章。每次你都可能有新的收获。
这一章的另一个重要特点是,我们需要有点“迂腐式的书生气”。因此,如果你发现某个部分的介绍很无聊,而且似乎只是在陈述显而易见的内容,请再仔细阅读一遍,以确保你完全理解它。
认识 CALCULAE 的作用
在 DAX 原理这一章中,你已经了解到有两种不同的上下文:行上下文和筛选上下文。我们到可以通过使用迭代器以编程方式创建行上下文,你应该也了解允许忽略筛选上下文的 ALL 函数。重要的是要记住,ALL 只是忽略筛选上下文,不会改变它。因此,在以下公式中:
[Sales Amount Margin] := SUMX ( ALL ( Sales ), Sales[SalesAmount] * AVERAGE ( Sales[MarginPct] ) ) //SalesAmount 和 MarginPct 是 Sales 表的两列,此处不需要纠结这两列的业务逻辑,它们的作用和普通列相同
ALL 忽略现有筛选上下文并始终返回整个表,但它不会改变公式其他部分的计值方式,事实上,在最内层的表达式中,AVERAGE 将在外部筛选上下文中计算 MarginPct 列的平均值。 DAX 中有一个可以更改筛选上下文的函数, 它就是 CALCULATE。
让我们从一个常用场景开始介绍 CALCULATE。假设你想要生成如图所示的报表, 其中包含产品类别、子类别和销售金额总和:
报告显示了每行占总计的百分比。你可以使用 Excel 数据透视表功能轻松地生成这样的报告,但是我们感兴趣的是将百分比作为度量值来计算,以便用户可以随时将其添加到数据透视表中。
下面是一个简单的解决方案:
SalesPct := DIVIDE ( SUM ( Sales[SalesAmount] ), SUMX ( ALL ( Sales ), Sales[SalesAmount] ) )
分子是 SalesAmount 的总和。分母忽略筛选上下文, 并且始终返回 SalesAmount 的总计, 而不考虑任何筛选器。只要不从切片器中选择任何内容, 此公式就可以正常工作。例如, 如果在切片器中选择黑色,则值是错误的。总计的百分比是 18.76% 而不是 100%, 因为用于百分比计算的分母是一个高于实际值的数字,如图所示:
这里的问题很容易理解。通过使用 ALL, 我们忽略了筛选上下文。因此,分母始终是所有销售的总计,而如果选择一种颜色,我们希望保留颜色上的筛选器,只清除类别和子类别上的筛选器。ALL 和迭代函数在这里不是正确的选择,我们需要一个更强大的函数,也就是 CALCULATE。
这一章引言写得真好。读大作的感觉
一旦得到了新的筛选上下文,CALCULATE 就在此上下文中计值第一参数(表达式),计值完成后恢复初始筛选上下文,返回计算结果。
老师 什么叫恢复初始筛选上下文
ALL 忽略现有筛选上下文并始终返回整个表,但它不会改变公式其他部分的计值方式,事实上,在最内层的表达式中,AVERAGE 将在外部筛选上下文中计算 MarginPct 列的平均值。 DAX 中有一个可以更改筛选上下文的函数, 它就是 CALCULATE。
老师好,上面一段我是从文中复制的。那DAX中是否存在以下规律:除了CALCULATE可以更改筛选上下文,一个DAX公式中的各个函数均在各自的筛选上下文环境中计值,互不影响。
案例里面,作为分子的SUM ( Sales[SalesAmount] ),这个值应该是Sales[SalesAmount] 这列的所有销售额的总和吧,而不是每一行的销售值,所以应该在分子前面加个Calculate??
“分母始终是所有销售的总计,而如果选择一种颜色,我们希望保留颜色上的筛选器,只清除类别和子类别上的筛选器”,这句话没有理解。选择了一个颜色,为什么要清除类别和子类别上的筛选器?这样不就是在类别子类别基础上加上颜色筛选吗?也就是筛选的子类子类别中黑色的这种产品啊。
老师,颜色的那个筛选器是怎么做的呀?
高老师,最后的公式里可以把all替换成allselected
老师你好,如果有个透视表的行标签是颜色和日期,颜色列的值是有重复的,颜色列对应的日期不重复,值是销售额。
我想求每个颜色下最小日期的销售额,并且透视表里每个颜色对应的销售额都是一样的(即最小日期的销售额)。
函数是这样的calculate(sum(表销售额),filter(all(表颜色列,表日期列),表颜色列=max(表颜色列)&&日期列=minx(filter(all(表),表颜色列=max(表颜色列)),表日期列))。
然后我设置了日期切片器,但是日期切片器却对这个表达式不能起到筛选的作用,如何优化,才能让每个颜色下的最小日期销售额随日期切片器的变化而动态变化。
老师,如果calculate的第一参数里再嵌套一个函数,例如averagex函数,这个averagex的第一参数为表,这个表是否受透视表行或列标签筛选影响? 谢谢。
高飞老师你好,我这边按照那个简单解决方案 敲了度量值,可是计算出来 分母不是所有销售额总计,切片器筛选颜色的时候,比如说案例里的黑色,计算出来是分子除以黑色销售额的总计,所以透视表最后的总计是100%,麻烦看下我哪里除了问题
感觉案例和本章的内容有点对不上啊。比如找不到Sales[SalesAmount]与Sales[MarginPct]字段
Hello,示例在哪里能下载?
[Sales Amount Margin] :=
SUMX (
ALL ( Sales ),
Sales[SalesAmount]
* AVERAGE ( Sales[MarginPct] )
)
第一个度量值中Sales[SalesAmount]和Sales[MarginPct]应该都受切片器等筛选器影响吧?