跳至正文

分析常识-工具篇: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)表内-行级别计算

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

  • 行级别计算:数据表明细行中,两个数字值计算、从一个日期中拆分年,或者计算两个日期差异。
  • 行级别过滤:所有的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

参考文献:

《分析常识-工具篇:DAX计算体系——计算的两大阶段、上下文体系》有6个想法

  1. Pingback: 分析常识-工具篇:从问题结构看SQL、DAX的共同性 - 喜乐君

  2. Pingback: 分析常识-工具篇:DAX中的上下文与计算体系(待修改) - 喜乐君

  3. Pingback: 分析通识-4:分析的“字段计算”分类(高难度) - 喜乐君

  4. 也就是基于整个字段列理解计算的环境,而非基于字段的每个值。这句话理解起来费劲了。基于整个字段列,不就是基于字段的每个值吗?

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

  6. Pingback: 《业务分析通识》_普适性的“业务分析框架”-喜乐君-敏捷BI布道师

评论已关闭。