分析常识-工具篇：DAX计算体系——计算的两大阶段、上下文体系

May 26, 2023 Revised
Jul 15, 2023 Lastest Update

本文的核心内容，最早于2023年3月杭州观远BI内部技术分享时介绍，大家可以一窥国产BI的距离

在上一篇（分析常识-工具篇：从问题结构看SQL、DAX的共同性 ），喜乐君介绍了SQL和DAX的典型分析结构，它们背后的“异中之同”是问题的共同结构特征和分析的本质。

在学习DAX的过程中，喜乐君对“迭代”（iteration）的概念持有保留意见，它和”上下文”一样，阻碍了知识的普及和理解，因此，喜乐君尝试用一个新的体系讲解适用于所有工具背后的计算体系，然后再将其对应到DAX的各类函数中来。在这里，会涉及到如下的关键概念：

• 计算的两个阶段：分析之前的计算统称之为“数据准备”，与之相对应的是“聚合分析”，在DAX中两个阶段的计算取名为计算列、度量值，即在创建时有不同的入口。
• 计算的两种类型（聚合视角）：包含聚合的计算称之为“聚合计算”，仅在单行中计算的称之为“行级别（非聚合）计算”，它们是最常见的两种计算，但仅仅是一个视角。
• 计算的两个类型（跨行视角）：跨行的计算称之为“跨行计算”，代表为聚合、排序计算；仅仅在单行内计算的，称之为“行内计算”或“行级别计算”（典型如YEAR函数、LEFT函数）。
• 计算的两个阶段、计算的两种类型，构成计算的“分类矩阵”，最多可以组成“2*2*2八类计算网格”
• 八类计算网格具有一定的先后关系，它们的组合就是DAX无尽的计算体系，特定的函数会调整其次序，比如CALCULATE，此时就会出现“上下文转换”或曰计算的优先级问题

暂时搁置迭代、上下文、表操作等复杂概念，基于这样的理解框架，是由浅入深理解DAX，理解Power BI分析的捷径。

本文只介绍上述全新框架中的基础内容，即：计算的两个阶段。喜乐君会在后续高级文章中，进一步增加计算的聚合视角分类、跨行视角分类，以及二者构成的分类矩阵，并进一步扩展为8种“计算网格”，再从8种类型讲解它们之间的相互关系，借助优先级概念讲解8种类型的组合（“上下文转换”）。

三、从计算的位置、阶段，理解DAX计算体系（入门）

May 23/25, 2023 Update

1、工具之上，所有计算的两个阶段、两类目的

不管是Tableau、SQL，还是Excel、DAX，我们都可以把计算粗略地分为两大类：准备性质的计算、分析性质的计算。所谓“准备”，是在数据表的阶段，预先为问题、聚合分析而预先的准备——因为聚合是分析的本质，是分析实现的最终归宿。而“分析性质”的计算，则是那些和问题紧密相关、随着问题交互可以随时变化的计算要素，特别是视图的筛选和聚合。

表哥表姐都熟悉excel的逻辑，Excel明细表中增加一个辅助列、计算列，比如Year函数、left函数，甚至vlookup函数，都可以视为“数据准备”计算——它们都是为了透视分析而预先完成的准备；而在透视表的过程中补充“字段”，比如“利润求和项/销售额求和项”、增加筛选器、切片器的过程，可以视为是“问题分析”——它们随着问题可以随时变化，筛选条件的变化会引起聚合结果的瞬间变化。

不考虑Excel和数据库之间的差异，而是把它们都是为关系数据的收集、整理、分析工具来讲，它们背后可以是相同的业务逻辑。Excel在明细行中的计算，可以对应SQL的JOIN和YEAR函数；excel透视表及其计算，可以对应SUM++WHERE+GROUP BY查询，以及包含其中的SUM(profit)/SUM(sales) 计算。

同理，DAX中，可以创建“计算列”（calculated column）预先完成各类准备性质的计算，其中从维度表lookup字段对应RELATED函数，YEAR函数和DATEDIFF函数也基本一致。excel透视表中的计算字段“利润率”,由于是随着筛选器随时变化的值，不能预先完成，因此要创建“度量值”（Measure）计算。

基于这样的理解，我们可以用如下的图示，进一步区分分析的两大阶段，并把每个阶段再细分为不同的计算场景。

“数据准备”和“问题分析”，不是函数上的差异，不是计算语法上的差异（比如DAX中计算列/度量值都可以写SUMX(“sales”.[Quantity]) ），而是阶段上的差异、环境的不同。所谓的“环境”，就是相同的函数返回不同结果的综合影响因素，官方普遍称之为“上下文”（context）。喜乐君更希望从问题的角度理解二者的阶段、环境：

• 数据准备的计算：和数据表明细行相对应，而和聚合问题无关的计算，比如YEAR函数，对于不同问题，它的计算结果都是完全相同的。这种不依赖于问题、依赖于数据表明细，称之为“准备阶段”
• 分析性质的计算：和明细行无关，而和问题紧密结合的动态计算，比如问题的筛选条件随时切换，并能引起聚合值的即时变化，正因为此，它们不能预先在数据准备阶段“固定写死”，而应该是动态的、逻辑的计算，称之为“分析阶段”。

在DAX中，数据准备的计算和分析性质的计算，分别创建了两个入口：计算列和度量值。部分计算二者通用，但一些计算会在两侧产生截然不同的结果。

注意，这里的两个阶段、两个位置、两个环境，对应两类计算目的，但并不与计算类型、计算函数相对应。

2、先看左侧数据准备：从数据表角度的进一步细分

从数据表的角度理解，“数据准备”阶段的计算又可以分为两个情形：单表计算、多表合并。二者本质都是数据集运算。

• 前者是单一数据表内部的计算，典型代表如“A.数量*A.单价”“YEAR(A.日期)”，由于计算发生在表内，所以喜乐君称之为“表内计算”（in-table calculation）；
• 后者引入了其他数据表，代表是Excel中的vlookup、SQL的join和DAX的Related函数，它们的目的都是把其他数据表的指定字段，引入当前数据表中并参与计算，因此称之为“跨表计算”（cross-table calculation），喜乐君倾向于把跨表计算称之为“数据表合并”。

常见的类型，可以如下图所示：

1）表内-行级别计算

这里先不考虑表内计算中包含聚合、排序的特殊形式，先只看单行的计算。按照是否筛选过滤可以分为两类情形 （喜乐：这一段表述不够严谨，后续有待调整）：

• 行级别计算：数据表明细行中，两个数字值计算、从一个日期中拆分年，或者计算两个日期差异。
  • Total_sales = Sales[Quantity]*Sales[Net Price]
  • Year_date = YEAR(Sales[order_date])
  • interval = DATEDIFF ( StartDate, EndDate, YEAR )
• 行级别过滤：所有的filter过滤都是行上的，过滤计算中，必然包含布尔性质的计算，也就是结果为TRUE/FALSE的计算。可以视为“行级别计算”和“仅保留TRUE”两个动作的结合，比如仅仅保留”Europe”的客户。

FILTER (
    Customer,
    Customer[Continent] = "Europe"
) 

2）多表合并

“跨表计算/多表合并”，则可以根据“被合并表”的特征进一步分类，既可是实体表，也可是逻辑表；既可以是“一端”的维度表，又可以是“多端”的事实表，所以跨表计算更加抽象。典型的类型如下：

• 从维度表合并维度的描述字段（多对一）：在当前事实表中，引入维度表中的描述性字段，比如在“销售明细表”中增加来自“产品档案表”的分类字段：RELATED(product[category])
• 与上面相反，从对应多行的另一个数据表中，引入指定字段并做聚合，从而和当前一端的数据表匹配，RELATEDTABLE函数引入表，聚合则指定字段计算

如下所示，这里的[Sales Amount]来自于表内两个字段的直接相乘，而[Sales at List Price]列表价来自于当前表和「Product」产品表价格的合并、相乘，[TX/Customer w/rel] 和[TX/Customer w/calc]则引用了「Sales」数据表行数而后聚合——后者默认是度量值，是在问题阶段完成的。

 --  RELATEDTABLE is an alias - albeit limited - of CALCULATETABLE
DEFINE
    COLUMN Sales[Sales Amount] =
        SUMX ( Sales, Sales[Quantity] * Sales[Net Price] )
    COLUMN Sales[Sales at List Price] =
        SUMX ( Sales, Sales[Quantity] * RELATED ( 'Product'[List Price] ) )
    MEASURE Sales[TX/Customer w/rel] =
        AVERAGEX ( Customer, COUNTROWS ( RELATEDTABLE ( Sales ) ) )
    MEASURE Sales[TX/Customer w/calc] =
        AVERAGEX ( Customer, COUNTROWS ( CALCULATETABLE ( Sales ) ) )
 

除了上述两类，还有一个关键的“包含聚合的计算列”，是理解DAX中计算列的关键。在这里，喜乐君倾向于把“计算列”中的聚合字段，视为是“当前明细表”和“聚合中间表”的合并形式，如同Tableau 的LOD计算，或者SQL的嵌套聚合查询。

比如计算列中 SUM(sales[quantity]) ，它会在明细表的每一行中增加整个数据表的销售数量总和，如下图所示，可以把第三列视为一个单行单列的聚合表，和数据表明细发生了join合并，因此计算列中的SUM(Sale[Quantity])会对应完全相同的：

下图，是喜乐君介绍Tableau LOD所使用的可视化图示，{FIXED cust_id : MIN(year(order_date)) } 可以视为是聚合表和当前明细表的逻辑合并，对应SQL的嵌套聚合查询。 DAX中包含聚合的计算列，也可以用这种数据合并的逻辑来理解。

如果不能理解数据表中为什么第三列的数据完全相同，DAX就还没摸到门道。相比“迭代”，数据表合并的方式更容易理解。

⚠️在上述分类中，我们还可以使用“表操作”的视角，把FILTER函数和RELATED视为表操作函数，不过这个视角与上述视角并不相融。后续喜乐君会单独写文章阐述，帮助读者从两个视角对比理解，查漏补缺。

综上所述，DAX中的计算列，就是在数据表阶段、在动态的问题分析之前，预先完成各方面的数据准备，不管是跨表合并、单表内聚合，每一个阶段都将真实地、可见地关联到每一行数据之后，所以说，“计算列”是行级别的计算——这里的行级别不是约定的单行内的计算，而是所有的计算最终都将变成单行内的一个具体值；换句话说，行级别是计算的环境、上下文，即context。

3、再看右侧的问题分析：分组聚合，和前后的数据筛选

从问题的结构角度看，问题必然包含筛选条件、问题维度（详细级别）和聚合答案三个部分。其中，问题维度决定聚合的多少，必然相当于问题而有意义；筛选决定聚合的大小，可以在聚合之前（类似于SQL- where，或者join），也可以在聚合之后（类似于SQL- having）。

1）影响聚合值的两类要素

虽然DAX把的筛选条件（filter condition）和分类依据（dimension）都称为“筛选上下文”（filter context），但二者作用其实不同；更由于计算上二者有明显差异，筛选可前、可后，分类必然在聚合之前，喜乐君强烈推荐加以区别对待。这是帮助初学者理解DAX的关键一步。

在这里，喜乐君把影响聚合值的所有要素，区分为筛选条件和分组条件两个部分。

• 分组条件Group-by：决定聚合值多少的关键因素，可以对应SQL的GROUP BY；DAX的代表是summarize相关函数。
• 筛选条件Filter condition：影响聚合值大小的所有因素，包括明细行的筛选、聚合后的过滤，对应SQL的where和having，特殊情况下，还有from中的join嵌套条件，以及limit子句。DAX中的典型代表是FILTER函数。

在Power BI中，分组条件常常由视图的行列维度来决定；而在DAX中，也可以使用ADDCOLUMN或SUMMARIZECOLUMNS等函数控制分组。如下所示，’Product'[Color]构成了最终聚合表的维度分类，三列聚合都以此为分类依据返回数据值，但各列聚合值的大小还要取决于内外部筛选条件，即’Product'[Color] = “Red”、ALL ( ‘Product'[Color] )的部分。

--  The compact syntax (boolean) is expanded in the full syntax
--  prior to the evaluation
DEFINE
    MEASURE [Sales Amount] = 
        SUMX ( Sales, Sales[Quantity] * Sales[Net Price] )
    MEASURE Sales[Red Sales] =
        CALCULATE ( [Sales Amount], 'Product'[Color] = "Red" )
    MEASURE Sales[Red Sales Full] =
        CALCULATE (
            [Sales Amount],
            FILTER ( ALL ( 'Product'[Color] ), 'Product'[Color] = "Red" )
        )
EVALUATE
SUMMARIZECOLUMNS (
    'Product'[Color],
    "Sales Amount", [Sales Amount],
    "Red Sales", [Red Sales],
    "Red Sales Full", [Red Sales Full]
)

点击链接可以运行（https://dax.do/Q9eEe3zFlFbC8F/ ）运算结果如下所示。

在这里，‘Product'[Color] = “Red” 是FILTER函数的简化，它如同SQL的where，虽然身处度量值之中，但却是对明细表数据行的条件判断，只有那些符合条件的数据才会被聚合。只是，这里的’Product'[Color] = “Red”同时抵消了分组字段的影响——因为DAX把分组字段和筛选条件都称为“FILTER CONTEXT”。

在POWER BI，还可以针对聚合后的数据表增加聚合筛选条件，这个过程，如同SQL的having，或者Tableau中的“度量筛选器”（相对于视图的数量筛选）。如下所示，视图之外的聚合值筛选，就实现了这个过程（左侧PBI，右侧Tableau）：

如果用DAX完全代码的方式实现聚合后的过滤条件，则对应FILTER，它既可以单独使用，也可以嵌套在Calculate等函数中使用，还可以用多种方式控制。如下所示：

对于初学者而言，推荐在计算中区分分组和筛选两个过程，特别是不同阶段的筛选条件，只有日渐熟练，然后再使用它们的组合和简化形式。

2）避免用单元格的方式理解计算的环境、上下文

在DAX中，所有影响最终聚合值变化的外部因素，统称之为该聚合值的环境、上下文，官方默认是以“单元格”的方式理解聚合值，这是喜乐君不推荐的。数据表table是数据查询的基础、数据关系的对象、可视化展现的依据，数据表是由字段列和明细行构成的，尽可能以这两个要素理解计算，而非“单元格”。

比如，在 the definitive guide to DAX一书中，作者举例如下，在“各个brand的total quantity”视图中，Contoso对应的3000数据值，包含三个filter contexts，分别来自于筛选器、交互筛选、brand字段值。Contoso和Litware对应的filter contexts当然是不同的。

喜乐君：最好的方式是从字段的角度去看，不管是明细表中的物理字段，还是问题聚合表中的逻辑字段。对于上面的题目而言，聚合quantity对应的filters context还是有三个，其中两个的作用是筛选（CY2007的交互筛选，和High school & Partial college的筛选器），另一个的作用是分组（brand）。站在Quantity聚合的角度看，我们说这个字段的filter context是三个，是一个完整的整体，而不要以为11个单元格对应11个不同的“上下文”。

也就是，基于整个字段列理解计算的环境，而非基于字段的每个值。

补充：有人在评论中留言说这两个视角似乎一样。其实大有不同，在excel中，我们引用一个字段列可以是 “B:B”，但是引用一个单元格就必须是“A2:B3”。同理，基于字段列理解计算，我们不关心分组的数据值有多少，只关心谁的角色是筛选、谁的角色是分组，所有单元格的“上下文”都是完全一样的。这是一个整体的理解方式。 站在字段的每个值的角度看，你则会以为10个单元格的上下文会各不相同。

我们要站在整个聚合表的视图看待filter contexts，同时把筛选和分组分开——它们对聚合值的影响不同。

四、站在高出，总体理解理解计算的两个位置

DAX的行上下文、筛选上下文，指的是两类计算的背景、环境，这两类计算可以称之为数据准备计算、问题分析计算。对于简单问题而言，数据准备就是行级别计算，问题分析的关键是分组聚合计算。

分析中，要明确的区分问题前的数据准备计算，和与问题结合的回答问题计算，前者是相对于数据表而言的，后者是相对于问题而言的，数据表和问题就是计算的背景、环境（context）。

可以用如下的图示来表述两个阶段，及其中的计算。左侧阴影区是聚合之前的准备，包括行级别计算（这里的行级别计算不同于Tableau之前所讲）和数据表合并，它们都是相对于数据表明细行的；右侧区域是相对于问题聚合的准备，包括多种筛选条件和分组聚合两大功能。相比之下，左侧数据准备是相对静态的、物理的，右侧是相对动态的、逻辑的。

1、使用简单案例理解两个阶段、两个环境context

前面讲解的简单案例“各产品类别的销售额总和”，算是最为简洁的一个模型。其中的行级别计算（Quantity * Unit Price）和数据表关系合并在左侧，它们可以通往非常多、截然不同的问题，因此说它们仅仅是相对于数据表而言的，而和具体的问题无关。

而该案例中的SUM计算，则必然要相对于问题而言，问题中的维度是聚合的分组依据、问题中的过滤条件影响聚合值的大小，它们就是聚合计算的环境（context）。

在DAX中，把准备计算所依赖的数据表明细行，称之为Row context，对应的计算是在数据表的每一行中完成，且有业务意义；而把聚合计算所依赖的问题环境，称之为filter context，对应的计算需要在问题中寻找依据，包括筛选器、切片、分组依据。

• Row context： 对应的是数据表中每一行（every row），即计算在每一行上完成，而且都有意义。
• Filter context：对应的是问题中影响聚合值大小的所有要素，包括filters筛选器、slice切片器、dimension视图维度。Power BI中的筛选器、切片器都可以理解为筛选条件，它们影响聚合值的大小；而视图维度主要影响聚合值的数量。

但要注意，和context对应的是不同阶段的计算，而非不同类型的计算。

2、区分两类context和两类计算类型

特别注意的是，计算所依赖的环境（context），和计算的类型无关。举例来说，“聚合计算”并非总是在右侧，也可以在左侧。正因为此，DAX才设计了计算列、度量值来严格区分两类计算阶段，让同一个计算可以有不同的结果。

如下所示，我们可以把常见的聚合计算/非聚合计算，和数据准备/问题分析两个阶段，组成四个象限的矩阵。同样是数量的聚合，第二象限的聚合会在每一行中增加相同的聚合值；第一象限的聚合则会随着问题的维度变化随时变化

第二象限的典型案例，是在数据表中预先构建的聚合，特别是RFM分析。

比如，为了完成一些高级分析，比如“不同购买频率的客户数量”，我们需要引入事先聚合好的数据表，或者使用预先聚合，在明细行中增加“每个客户的订单计数”，此时聚合发生在问题的聚合之前，属于问题分析的准备，计算依然是在行级别有效的（row context）。这个问题，对应Tableau的LOD表达式（{fixed [customerID]: COUNTD(orderID)}），或者DAX中使用聚合的计算列。

DEFINE COLUMN
DAX_Frequence=  Calculate (DISTINCTCOUNT(sales.[orderID]),  
                Values([customerID])
                )

此类明细表中预先聚合，比聚合过程中引用行级别筛选条件，往往更难以理解。

在实践中，计算的复杂性通常来自于函数及context的组合。不考虑函数嵌套的情况下，两类context对应两类计算，就有四种组合（小节开始的四个象限）；如果考虑到嵌套（比如聚合计算中嵌套行级别计算），则会更多。

至此，本文就尝试脱离迭代计算本身，改用通识的概念解释了DAX的两个计算阶段，这是DAX相比SQL的明显进步之处。

May 23, 2023 V2.0 Revise
May 26, 2023 V2.1 Update重写第一部分2-3，补充四
Jul 15, 2023 Lastest Update

参考文献：

• Microsoft：DAX function reference
• SQLBI：Row Context and Filter Context in DAX