【致知篇57】DAX CALCULATE vs. Tableau LOD：从SUM+IF条件计算到SUMIF

01—不同工具中的SUM+IF

在2022年写作《数据可视化分析2.0》的过程中，我补充了此前的一个关键知识盲区：指标分类、条件计算。

聚合是分析的本质过程，聚合度量的业务形态则是指标。指标因计算的复杂度 不同而有了抽象度差异，常见的几个类型如下：

• 销售额总和：直接聚合
• 利润率(利润总和/销售额总和):聚合的二次计算
• MTD销售额：包含计算条件的聚合
• MTD销售额同比：包含多个计算条件的聚合及其计算

理解了这些指标的差异，才能理解更复杂的指标逻辑，比如消费金融的LOSS%、C-M1、M2+@MOB0-6这样的指标体系。

从计算的复杂性来看，第三类及之后的类型是难点，也是大数据计算的性能“陷阱”。特别是一个问题中同时包含了多个这样的指标，不同计算的方式就有了天壤之别的性能差异。比如：

2022年，各个类别、子类别 的

YTD销售额(总和)及其同比、MTD销售额及其同比、利润率

在新书第六章筛选章节，我补充了“独立筛选和计算条件筛选”这两个类型，前者对问题中的所有指标起作用，后者仅对与之结合的聚合起作用；前者是性能的积极因素，后者通常则是消极因素——不同的优化能力，体现了分析师、工具之间的差异。在这一点上，近似编程的DAX确实是更胜一筹，其次是高级灵活的SQL，不过二者都需要理解原理、技能卓越的分析师才能发挥最大价值。

在没有学习PBI的 DAX之前，我误以为只有一种条件计算的逻辑，就是SUM+IF，殊不知在Excel及其体系中，还有一个性能优化plus方案——SUMIF方案。

先说最易于理解的方案：SUM+IF，对符合IF条件的数据行聚合相加。以“本年的销售额总和”为例，初学者使用Excel、SQL和Tableau会用如下的计算实现。

 Excel SUM+IF：

=SUM(IF YEAR(T2:T100)=2022,S2:S100,0) )，

其中T列是订单年度，S列是销售额

 SQL SUM+IF:

SELECT 
SUM( IF (YEAR([订单日期])=2022, [销售额], null ) as YTD
FROM table

 Tableau SUM+IF:

SUM( IIF (YEAR([订单日期])=2022, [销售额], null )

在上述三个方法中，SUM和IF借助于括号嵌套而结合在一起，IF优先于SUM。由于SUM和IF完全独立，从计算的角度看，SUM对不符合计算条件的null或者0值也要执行相加计算——这一点非常重要，后面SUMIF的优化方案就是从这里而来。

我在2021年5月写过一篇博客，题名“【高级】Tableau性能优化之关键：两种计算对性能的影响实证”（见于yupengwu.com），介绍过不同的筛选方法对性能的巨大影响。上述SUM嵌套IF的方式就是影响大数据性能的关键场景之一。

如何优化SUM+IF的计算性能呢？

很久之前，Excel、SQL都给出了方案，只是业务用户不好察觉，这种区别于SUM+IF的形态，我通称之为SUMIF方案——虽然IF依然先于SUM计算，但却不再独立，而是紧紧结合在一起。

理解SUMIF相对于SUM+IF的优化特征，是理解Tableau和PBI的方向性不同，特别是理解Calculate表达式（聚合表达式+filter过滤条件）的关键。

02—SUMIF优化方案

SUM+IF之所以慢，不在于IF返回的结果是0还是null，关键在于聚合计算需要遍历、依次访问数据表的每一行（PBI中称之为迭代iterate，以后单独介绍）。对于动辄几百万、上千万的数据而言，遍历整个表会消耗很多的算力资源，对于结果却毫无用处。

也许是这个原因，Excel就有了一个SUMIF函数，看上去它只是SUM和IF的组合形式，但是从聚合角度看，它优化了计算逻辑，跳过了不符合条件的数据行。

大家可以用如下的图示理解SUM+IF和SUMIF之间的区别：

虽然二者条件完全相同，但是SUM+IF中的SUM要对数据表的所有行执行聚合（iterate the table and then aggregate），因此消耗大量无效算力；而SUMIF只对符合条件的有限数据行执行聚合（iterate the table, filter rows and then aggregate)，避免了算力浪费。

如果把上面的3行数据扩展到3000万行，性能之间的巨大差异往往会导致数据库资源长期占用，甚至拖垮一个分析工具。

当然，除了Excel，SQL也有完全相同的逻辑，它是借助于WHERE把条件前置到GROUP BY和SUM聚合之前。上述“2022年的销售额总和”，就有了第二种写法：

• Excel SUMIF：

= SUMIF(T:T,2022,S:S)，其中T列是订单年度，S列是销售额

• SQL SUMIF：

SELECT 
SUM( [销售额] )
FROM table
WHERE YEAR([订单日期])=2022

这个问题非常简单，没有分类字段、指标单一，如果是如下的复杂的问题，SQL中还要实现上述的SUMIF效果，就需要一个全新的功能——嵌套聚合查询。

2022年，各个类别、子类别 的

YTD销售额(总和)、MTD销售额、利润率

可以如下实现（MTD简化为2022年12月）：

SELECT 
SUM( a.[销售额] ) as YTD sales,
FROM table a
JOIN 
(SELECT 
SUM( b.[销售额] ) as MTD sales,
FROM table b
WHERE YEAR(b.[订单日期])=2022 and MONTH(b.[订单日期])=12
GROUP BY b.类别，b.子类别
) on  a.类别 = b.类别，a.子类别=b.子类别
WHERE YEAR([订单日期])=2022
GROUP BY a.类别，a.子类别

当然，我对SQL所知尚浅，应该还有其他类似、甚至性能更优的方案，它们都把YTD与MTD的条件置于WHERE中，而非SELECT的SUM+IF的。

从SQL的优先级来看，WHERE是早于GROUP BY，也早于SELECT的，这样就实现了下图中右侧的计算方案：

理解了上述EXCEL、SQL的过程，就可以轻松地理解，为什么DAX设计了一个专门的CALCULATE表达式，并且把FILTER条件置于其中（参见上图右侧的逻辑代码）。

CALCULATE（聚合表达式, FILTER条件）

通过在每一个度量中单独指定filter计算条件，DAX可以在引擎中将其优先级提前，从而避免了SUM+IF的低下性能。也正因为此，我个人通常把CALCULATE称之为表达式（expression），而非函数，强调两个内容的组合关系——换一个角度，预设功能的封装都是函数，不过这样不易于理解组合关系。

03—CALCULATE和LOD的差别

从计算的角度看，CALCULATE表达式确实代表了极高的逻辑水平，它为优化大数据性能提供了一个绝佳方案，是大数据分析的代表作。它在POWER BI中的位置，犹如LOD之于Tableau。

二者的共同点是，产品经理总结了分析中高频的分析需求，然后将其封装为不同的函数。只是Tableau向左——把维度分类字段封装到FIXED表达式中，而POWER BI向右——把筛选条件封装到CALCULATE表达式中。

因此，LOD解决了不同详细级别的问题合并的问题，CALCULATE解决了不同范围的指标（度量值）的计算优化问题。

不管是何种语法，前端的表达式都将转化为数据库层面的SQL查询，因此从SQL的角度看二者的共同点，二者都是嵌套查询（nested query）和数据合并（join）的组合形式。

不过，从业务的角度看，Tableau 的LOD表达式有助于回答“多维分析”的问题，典型代表是RFM分析，以及我个人钟爱的购物篮关联分析（你很难找到更好的方案）；而Power BI的Calculate解决的是筛选的问题，它引出了一个难点，如何调节calculate内部筛选（internal filters）和视图中外部筛选（external filters）的关系，于是就有了ALL、ALLexcept等多个调节符，考虑到Power Bi把筛选条件和分类维度都视为filter context，这一逻辑影响了业务用户理解问题中的层次关系。

也正因此，我个人之见，Tableau是业务方向的，Power BI是技术方向的。

那些努力模仿、学习，并试图超越它们的国产BI工具，至少应该先思考自己的方向，然后再有针对性地借鉴，特别是理解不同技术路线给业务带来的负面性，才能完成超越。

当然，从目前的情况看，我们还在“摸石头”，只知其表，未达其里，更何谈过河呢。 新年喜乐平安