【喜乐君按】作为BI工具的两大巨擘，Tableau和Power BI都拥有相当的用户和粉丝，很多人非常关心二者的区别，于是就有了喜乐君“Power BI向左、Tableau向右”的对比系列。本文最早写于2023年春节，6个月之后，补充修正，并作为“Tableau VS Power BI”对比系列的重要组成部分。一年之后，再度修改 V3，将奠定喜乐君理解表达式、context 的基础。
本文最早V1版本发布在“Tableau传道士”，文章：https://mp.weixin.qq.com/s/ALxtB5IqGYv2fRudVjvc1Q。博客持续更新。
LOD表达式是Tableau产品具有划时代意义的关键产品，自从9.0版本发布以来，至今依然被分析师津津乐道；而CALCULATE表达式则是Power BI“创建度量值”生成的表达式。从技术上看，二者都是分析函数发展的里程碑，只是方向截然不同——LOD走向了多个“详细级别”的多维分析，CALCULATE走向了多重筛选条件，并发展了CONTEXT和CONTEXT Transition一系列概念。
为了帮助理解CALCULATE表达式，本文从Excel中的SUM+IF和SUMIF说起。
give me a coffee /
一、透视图从SUM+IF到SUMIF：完全独立、相对独立和条件筛选
在2022年写作《数据可视化分析2.0》（京东已经发布 2023.9）的过程中，我补充了很多之前的知识盲区，特别是指标分类（metric）和条件计算（conditional calculation）。分析的本质过程是聚合，聚合度量的业务形态则是指标，指标的难点在于与外部条件的组合。因此，指标因计算的复杂度不同而有了抽象度差异，常见的几个类型如下：
- 一类指标：直接聚合，如 销售额总和
- 二类指标：聚合的二次计算，如利润率(利润总和/销售额总和)
- 三类指标：包含筛选条件的聚合及其比较，如MTD销售额、YTD销售额、MTD销售额同比 （聚合前面指标了筛选范围）
- 四类指标：“钻石客户数量”：其中“钻石客户为累计间夜60+且积分10万+”（在筛选条件基础上增加预先聚合）
从计算的复杂性来看，第三类及之后的类型是难点，也是大数据计算的性能“陷阱”，比如消费金融的LOSS%、C-M1、M2+@MOB0-6这样的指标体系。特别是一个问题同时包含了多个此类指标，不同计算的方式就有了天壤之别的性能差异。比如：
2022年，各个类别、子类别 的 YTD销售额(总和)及其同比、MTD销售额及其同比、利润率
在很长一段时间中，我误以为只有一种“条件计算”的逻辑，就是SUM+IF，殊不知在Excel及其体系中，还有一个性能优化方案——SUMIF方案，我个人倾向于把它视为“Excel 版本的表达式 Expression”。
基于SUMIF才能更好地理解CALCULATE表达式的精髓——千变万化，不离其宗。
1、SUM+IF的透视表方案：逻辑判断作为聚合前提，完全独立
先说最易于理解的方案：SUM+IF，对符合IF条件的数据行聚合相加——特别重要的是，这里的 SUM 和 IF 是分离的。以“Florida州的销售数量总和”为例，初学者使用Excel、SQL和Tableau会用如下的计算实现。
- 透视表的逻辑：筛选和聚合相互独立
分析的本质是聚合，聚合对应Excel的透视表、SQL的分组聚合（group by）、BI工具的视图。因此，在Excel中，标准的做法是基于数据表明细行创建透视表，然后将“Quantity”和“State”依次拖入“值”和“筛选器”区域之中。如下所示：
透视表pivot table天然地实现了筛选条件和聚合相互独立。独立的好处是性能更好，但在包含不同范围的问题中会缺乏一些灵活性，比如“东北地区，各品牌的当年销售额、上年销售额及同比”。由于多个度量值对应不同的范围，此时透视表的“筛选器”就难以区别对待。于是，更具有灵活性的、字段级别的SUM+IF表达式方案组合就出来“救场”了。
2、SUM+IF表达式方案：条件判断和聚合相对独立
和透视表中筛选条件单独出来不同，这里将每个度量的聚合条件置于字段级别——SUM+IF 在自定义字段时实现，不过，从计算上看，二者也是相对独立的环节。
举例说明，计算“Florida州的销售数量总和” 和 “NewYork 的销售额数量总和”两个，由于两个“度量”对应不同的筛选条件，筛选条件还是基于相同字段，因此就可以先用IF做两个辅助列（即DAX中的计算列），然在再使用透视表对辅助列做聚合。如下图所示：
究其本质，是将筛选作为了聚合值的一部分——逻辑判断影响聚合的对象，而非作为聚合的前提。它不是真正意义上的筛选，而是通过“篡改数据值”的方式，让不符合条件的数据值随被聚合、但等同没有。
这种方式的优势是灵活——字段级别的“筛选”；代价是性能——因为不符合条件的行也会被相加（常常以0参与计算）
需要补充说明的是，这里“IF判断是在数据表明细行完成的”，而聚合过程，是在“透视表阶段”完成的。到了DAX，某些专家为它们赋予了Row CONTEXT和FILTER CONTEXT的名字，结果搞晕了很多人。
3、SQL 中的独立筛选
当然，除了Excel，SQL也有完全相同的逻辑，它是借助于WHERE把条件前置到GROUP BY和SUM聚合之前。上述“2022年的销售额总和”，就有了第二种写法：
- SQL SUMIF：
SELECT SUM( [销售额] )FROM tableWHERE YEAR([订单日期])=2022这个问题非常简单，没有分类字段、指标单一；如果是如下的复杂的问题，SQL中还要实现上述的SUMIF效果，就需要一个全新的功能——嵌套聚合查询。
2022年，各个类别、子类别 的YTD销售额(总和)、MTD销售额、利润率
可以如下实现（MTD简化为2022年12月）：
SELECT a.类别，a.子类别
SUM( a.[销售额] ) as YTD sales,
FROM table a
JOIN
(SELECT b.类别，b.子类别,
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查询中，每一个SUM都没有和IF直接组合，而是皆有嵌套子查询的WHERE筛选完成条件筛选，WHERE优先于分组聚合，因此有助于优化性能。当然，我对SQL所知尚浅，应该还有其他类似、甚至性能更优的方案。
参考上述EXCEL、SQL的完全独立的过程，DAX设计了一个专门的CALCULATE表达式，并且把FILTER条件置于其中，从而构成了如下的表达式语法。
CALCULATE（聚合表达式, FILTER条件）通过在每一个度量中单独指定filter计算条件，DAX可以在引擎中将其优先级提前，从而避免了SUM+IF的低下性能；同时，又保留了SUM+IF相比透视表筛选器所具有的灵活性，可为一举两得。也正因为此，我个人通常把CALCULATE称之为表达式（expression），而非函数（function），强调它两个功能组合而来、用于完成特定分析需求。
二、行级别的条件聚合：从SUM+IF，IF+SUM 到SUMIF、 SUMX
SQL中，类似于 Excel 透视表的筛选完全独立方案，是把条件放在 where 中；而类似于透视表 SUM+IF的方案，则是在聚合对象中增加逻辑判断条件。where 是完全的独立，是聚合的前提；sum+if 是相对独立的方案，if 是影响聚合的对象。
SELECT SUM( IF (YEAR([订单日期])=2022, [销售额], null ) as YTD FROM table当然，在Tableau中，我们亦然可以使用类似于 SQL 的逻辑， SUM+IF在视图中如下所示:
SUM( IIF (YEAR([订单日期])=2022, [销售额], null )在上述三个方法中，SUM和IF借助于括号嵌套而结合在一起，IF优先于SUM。由于SUM和IF完全独立，从计算的角度看，SUM对不符合计算条件的null或者0值也要执行相加计算——这一点非常重要，SUMIF的优化方案就是从这里而来。
在《数据可视化分析2.0》第六章筛选章节，我补充了“独立筛选和计算条件筛选”这两个类型，前者对问题中的所有指标起作用，后者仅对与之结合的聚合起作用；前者是性能的积极因素，后者通常则是消极因素——不同的优化能力，体现了分析师、工具之间的差异。在这一点上，近似编程的DAX确实是更胜一筹，其次是高级灵活的SQL，不过二者都需要理解原理、技能卓越的分析师才能发挥最大价值。
如何优化SUM+IF的计算性能呢？
很久之前，Excel、SQL都给出了方案，只是业务用户不好察觉，这种区别于SUM+IF的形态，我通称之为SUMIF方案——虽然IF依然先于SUM计算，但却不再独立，而是紧紧结合在一起。
理解SUMIF相对于SUM+IF的优化特征，是理解Tableau和PBI的方向性不同，特别是理解Calculate表达式（聚合表达式+filter过滤条件）的关键。
1、Excel 行级别的聚合SUMIF：逻辑判断跳出行范围
当然，强调要分开计算，是为了避免与下面这种明细表中一次性计算的方法相区分。 如果在明细行中使用SUM+IF的计算，每个单元格使用如下的判断，则相当于在数据表明细行中完成“Florida的销售数量”计算，行级别+IF+聚合，结果会在每一行上有有意义，但又具有了透视表的聚合结果！
如下图所示，在行级别中增加Florida 的数量，简化后的表达式是：=SUMIF(N:N="Florida",R:R,0) )，如果你能理解这里，恭喜你，你已经理解了Power BI和DAX中最为烧脑的题目之一——“在计算列中使用聚合”。
当然，既然有前面（1）辅助列、（2）透视表的基础，我们也可以把上面的（3）行级别聚合结果，视为是透视表 Vlookup到明细表中的数据合并形式——这是理解迭代的真谛。只是目前的这种理解容易把问题复杂化，因此这里暂且按下不表。
2、Excel 行级别的 IF+SUM 方案：
需要特别注意的是，这里有一种看似正确、实则错误的方案：行级别的IF+SUM（IF 优先），如下所示：
=SUM( IF(N:N="Florida",R:R,0) )这个方式会仅在满足条件的行上返回正确的结果，即 Florida 对应的行后面返回7，否则返回0. 究其原因，
这一点理解起来咯有难度，后期结合 context 另外介绍。
（图片需要补充）
3、SUMIF优化方案：独立于聚合之外的直接筛选
SUM+IF之所以慢，不在于IF返回的结果是0还是null，关键在于聚合计算需要遍历、依次访问数据表的每一行（PBI中称之为迭代iterate）。对于动辄几百万、上千万的数据而言，遍历整个表会消耗很多的算力资源，对于结果却毫无用处。
我在2021年5月写过一篇博客，题名“【高级】Tableau性能优化之关键：两种计算对性能的影响实证”，介绍过不同的筛选方法对性能的巨大影响。上述SUM嵌套IF的方式就是影响大数据性能的关键场景之一。
也许是这个原因，Excel就有了一个SUMIF函数，看上去它只是SUM和IF的组合形式，但是从聚合角度看，它优化了计算逻辑，跳过了不符合条件的数据行。而且赋予了 SUM 优先位置。
大家可以用如下的图示理解SUM+IF和SUMIF之间的区别：
- 左侧，SUM+IF 方案：IF只是修改了聚合对象的值，以修改方式实现“筛选”，逻辑判断优先，但并没有筛选。
- 右侧，SUMIF 方案：IF 判断是聚合对象的条件，而非聚合的前提。不符合条件的明细行在聚合前被过滤掉了。
虽然二者条件完全相同，但是SUM+IF中的SUM要对数据表的所有行执行聚合（iterate the table and then aggregate），因此消耗大量无效算力，这里的 SUM+IF 更像是 SUM+辅助列，没有筛选过程；而SUMIF只对符合条件的有限数据行执行聚合（iterate the table, filter rows and then aggregate)，它是真正的筛选、聚合，避免了算力浪费。
如果把上面的3行数据扩展到3000万行，性能之间的巨大差异往往会导致数据库资源长期占用，甚至拖垮一个分析工具。
4、DAX 版本的 SUMIF：SUMX 表达式
虽然上面 SUMIF 的方式真香，但是这个嵌套在 DAX 是无法实现的，因为它难以理解嵌套的优先级。所以就有了 SUMX 方案：将聚合条件和聚合对象分离。
SUMX ( <Table>, <Expression> )
在DAX中，就有了 行级别的SUMX 形式。
下面内容，准备拆分出来（Sep 7, 2023）
三、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工具，至少应该先思考自己的方向，然后再有针对性地借鉴，特别是理解不同技术路线给业务带来的负面性，才能完成超越。
当然，从目前的情况看，我们还在“摸石头”，只知其表，未达其里，更何谈过河呢。
修订记录：
- 2023-02 正月初三 ，V1，学习 DAX 有感，先有此文，并发微信公众号“Tableau传道士”，文章：https://mp.weixin.qq.com/s/ALxtB5IqGYv2fRudVjvc1Q
- 2023-09-07， V2.0 大幅度修正本文，强化透视表、SUM+IF、SUMIF的逻辑连贯性
- 2024-07-17 ，V3，全面修改，分为透视表/问题级别，和行级别两大部分，分别通往 sumx 和 Calculate 两个表达式。后期考虑拆分为两篇文章。