DAX权威指南：运用Power BI、SQL Server Analysis Services和Excel实现商业智能分

本书是微软DAX语言在商业智能分析、数据建模和数据分析方面的指南。通过对本书的学习，你将了解如何使用DAX语言进行商业智能分析、数据建模和数据分析；你将掌握从基础表函数到高级代码，以及模型优化的所有内容；你将确切了解在运行DAX表达式时，引擎内部所执行的操作，并利用这些知识编写可以高速运行且健壮的代码。本书第2版的重点内容包括基于免费的Power BI Desktop来构建和运行示例，帮助你在Power Bl、SQL Server Analysis Services或Excel中充分利用强大的变量（VAR）语法。你想要使用DAX所有的强大功能吗？那么这本未进行任何删减、深入浅出的著作正是你所需要的。本书适合Excel高级用户、商业智能分析人员、使用DAX和微软分析工具的专业人士。

译者序：欢迎来到DAX的世界
你好！我是本书的译者，高飞。
很高兴你打开了这本书，很荣幸由我来为你转述关于DAX语言的所有秘密。
我猜你已经用过Excel Power Pivot、Power BI或者SQL Server Analysis Services，并且惊叹于它强大的数据存储和运算能力。没错，在这些工具中，你都可以使用DAX对数百万、上千万行甚至更多的数据展开灵活的分析，这是一门跨Excel、商业智能和企业级工具的分析语言。
DAX就像它的全称Data Analysis eXpression所描述的那样，是一门用于数据分析的函数式语言。是的，DAX和我们所熟悉的Excel公式都是基于函数的表达式，为了便于用户快速上手，DAX中的很多函数都直接照搬自Excel。所以，只要你熟悉Excel，DAX便会让你产生一种亲切感，甚至可以快速实现一些常见的计算。
但是这种便利性仅限于起步阶段。DAX与大多数编程语言不同，它有很多独特且重要的理论，比如计值上下文、迭代和上下文转换等，理解这些概念是决定你能否掌握DAX的关键。相信这也正是你打开本书的原因。The Definitive Guide to DAX: Business intelligence with Microsoft Power BI, SQL Server Analysis Services, and Excel在全球被奉为学习DAX的经典书目，其权威程度超过微软官方文档。它的知识框架足够系统和全面，在广度和深度这两个层面，都是当之无愧的NO.1。可以说，读懂了这本书，你就“解锁”了DAX的所有秘密。
DAX由微软的SQL Server Analysis Services团队开发，作为代号Gemini项目的一部分，在2009年与PowerPivot for Excel 2010插件一起发布。当时微软敏锐地觉察到了自助分析的浪潮即将到来，其基于自身在数据库领域的深厚积累，在MDX、SQL和Excel公式的基础上开发出了全新的DAX语言。随着大数据时代的到来和商业智能产品的流行，这项投入被证明是极富远见的。在本书中，我荣幸地邀请到了目前担任微软首席工程经理，同时也是开发DAX编程语言和查询处理器的主导人物——Jeffrey Wang为本书作序。
在翻译本书的过程中，我多次与两位作者Marco Russo和Alberto Ferrari通过邮件往来，询问技术细节和甄别勘误，两位作者的热情和耐心给我留下了深刻的印象。在他们的帮助下，本书中文版本修订了原著中的多处错误，相信在全球范围内，本书中文版很可能是目前质量最好的一个版本。
在此，我要感谢电子工业出版社的编辑王静老师的信任和支持。很多读者可能不知道，本书的翻译工作始于2017年夏天，我利用业余时间，花了一年多完成了本书第1版的翻译。巧合的是，此时本书第2版也传来了即将发布的消息，我们经过短暂的商议决定放弃已经翻译好的第1版，继续翻译第2版，于是，又是一年多的时间，这一轮横跨4年，两个版本的漫长翻译工作才终于落下帷幕。
坦白地讲，翻译工作本身是枯燥的，尤其是面对这么一本大部头的技术型著作，唯一能让我坚持下去的动力就是它给读者们带来的价值。所以，虽然道阻且长，我却始终不敢掉以轻心。为了保证所有章节在行文和逻辑方面的一致性和准确性，全书由我一人翻译完成，这样可以最大程度地保证翻译质量，但是这也意味着要付出更多的时间。感谢各位读者的理解和等待。在此，也要感谢所有参与校对工作的编辑们，你们的付出进一步提高了本书的质量。
Power BI和DAX的流行离不开用户的无私分享和社区的壮大，在国内，很多微软热心人士、企业和Power BI爱好者都为此贡献了自己的力量，在我的心目中有一个长长的感谢名单，因篇幅所限，无法在此一一列举，谨邀请其中两位微软MVP：敏捷艾科创始人，Power BI可视化比赛的推动者赵文超和ExcelHome创始人周庆麟老师为本书作序，以表感谢。
最后，我必须要郑重感谢的一个人是我的老婆，正是你的支持和鼓励，允许我占用属于周末和节假日的二人时间，我才得以完成这项浩大的工程。
由于水平所限，书中难免会有不合理甚至错误之处，欢迎读者朋友批评与指正。如果你对这本书有任何疑问，可以访问“Power BI极客”（powerbigeek）网站的DAX圣经专区留下你的宝贵意见。
现在，DAX的旅程即将开始，你准备好了吗？
高飞
2021年1月于 上海


前  言


也许你不知道我们的名字。但你在日常工作中使用的软件都出自我们编写的代码：，如Power BI、Excel Power Pivot、SQL Server Analysis Services……等，都出自我们编写的代码。是的，我们是DAX语言和VertiPaq引擎的幕后作者。
你在本书中学习的语言是我们创造的。我们花费数年来研究这门语言，优化引擎，寻找改进优化器的方法，并试图将DAX构建为一种简单、干净、合理的语言，以使身为数据分析师的你工作得更加轻松、更加高效。
但是，这只是本书的前言，就别再谈论自己我们了!为什么我们要为Marco和Alberto，这些来自SQLBI的家伙写出版的书作序呢？因为当你开始学习DAX时，你只需要在网上点击几下就能找到他们写的文章。当你开始阅读他们的著作，学习DAX时，希望也能感谢我们的辛勤工作。多年前我们曾经会过面过，Marco和Alberto对SQL Server Analysis Services的深入了解让我们非常钦佩。在DAX诞生初期，他们是第一批学习并使用这种新引擎和语言的人。
他们发表的文章、论文和博客，成为成千上万人学习的源泉。虽然我们虽然编写了代码，但是却我们没有太多时间教人们如何使用它，他们才是传播DAX知识的传播者人。
Alberto 和 Marco的著作一直都是在这个领域一直都很的畅销书，现在，有了这本全新的DAX指南，他们真正创作出了一本关于我们创造作和热爱的语言的里程碑式出版物。我们编写代码，他们撰写书，你学习DAX并为你的企业提供前所未有的分析能力。我们喜欢这种方式：我们、他们和你像一个团队一样紧密工作，从数据中获得更好的见解。

Marius Dumitru，架构师，Power BI首席技术官办公室
Cristian Petculescu，Power BI首席架构师
Jeffrey Wang，首席软件工程师
Christian Wade，高级项目经理　　

目  录 


第1章  DAX是什么	1
理解数据模型	1
理解关系的方向	3
给Excel用户的DAX学习建议	5
单元格和智能表格	5
Excel函数和DAX：两种函数式语言	7
使用迭代器	7
DAX相关理论	8
给SQL开发人员的DAX学习建议	8
处理关系	9
DAX是函数式语言	9
DAX是一种编程语言和查询语言	10
DAX和SQL中的子查询与条件语句	10
给MDX开发者的DAX学习建议	11
多维模型和表格模型	12
DAX是一种编程语言和查询语言	12
层级结构	12
叶级计算	14
给Power BI用户的DAX学习建议	14

第2章  DAX介绍	15
理解DAX计算	15
DAX的数据类型	17
DAX运算符	20
表构造器	22
条件语句	22
理解计算列和度量值	23
计算列	23
度量值	24
正确选择计算列和度量值	27
变量	28
处理DAX表达式中的错误	29
转换错误	29
算术运算错误	30
空值或缺失值	30
截获错误	32
生成错误	35
规范化DAX代码	36
聚合函数和迭代函数介绍	39
认识常用的DAX函数	42
聚合函数	42
逻辑函数	43
信息函数	45
数学函数	45
三角函数	46
文本函数	46
转换函数	48
日期和时间函数	48
关系函数	49
结论	51

第3章  使用基础表函数	52
表函数介绍	52
EVALUATE函数语法介绍	54
理解FILTER函数	56
ALL和ALLEXCEPT函数介绍	58
理解VALUES、DISTINCT函数和空行	63
将表用作作为标量值	68
ALLSELECTED函数介绍	70
结论	72

第4章  理解计值上下文	73
计值上下文介绍	74
理解筛选上下文	74
理解行上下文	79
测试你对计值上下文的理解	81
在计算列中使用SUM函数	81
在度量值中使用列	83
使用迭代函数创建行上下文	83
嵌套多个表的行上下文	84
同一个表上的多层嵌套行上下文	85
使用EARLIER函数	90
理解FILTER、ALL函数和上下文交互	91
使用多个表	94
行上下文和关系	95
筛选上下文和关系	98
在筛选上下文中使用DISTINCT和SUMMARIZE函数	102
结论	105

第5章  理解CALCULATE和CALCULATETABLE函数	107
CALCULATE和CALCULATETABLE函数介绍	107
创建筛选上下文	108
CALCULATE函数介绍	111
使用CALCULATE函数计算百分比	116
KEEPFILTERS函数介绍	126
筛选单列	130
筛选复杂条件	131
CALCULATE计值顺序	135
理解上下文转换	139
行上下文和筛选上下文回顾	139
上下文转换介绍	142
计算列中的上下文转换	145
度量值中的上下文转换	148
理解循环依赖	151
CALCULATE函数调节器	155
理解USERELATIONSHIP函数	155
理解CROSSFILTER函数	158
理解KEEPFILTERS函数	159
理解CALCULATE函数中的ALL函数	160
无参数的ALL和ALLSELECTED函数介绍	162
CALCULATE规则总结	163

第6章  变量	165
VAR语法介绍	165
变量是常数	167
理解变量的范围	168
使用表作为变量	171
理解惰性计算	173
使用变量的常见模式	174
结论	176

第7章  迭代函数和CALCULATE函数的使用	177
迭代函数的使用	177
理解迭代的基数	178
在迭代函数中使用上下文转换	180
CONCATENATEX函数的使用	184
返回表的迭代函数	186
使用迭代函数解决常见问题	189
计算平均和移动平均	189
RANKX函数的使用	192
改变计算的颗粒度	200
结论	204

第8章  时间智能计算	205
时间智能介绍	205
Power BI中的“自动日期/时间”	206
Excel Power Pivot中的自动日期列	207
Excel Power Pivot中的日期表模板	208
创建日期表	208
CALENDAR和CALENDARAUTO函数的使用	209
多个日期表的使用	212
处理连接到与日期表的多个关系	212
处理多个日期表	214
理解基础时间智能计算	215
标记为日期表	219
基础时间智能函数介绍	221
计算年初至今、季度初至今和月初至今	222
计算平移后的周期平移	224
嵌套混合使用时间智能函数	227
计算周期之间的差异	229
计算移动年度总计	231
为嵌套的时间智能函数选择正确的调用顺序	232
理解半累加计算	234
使用LASTDATE和LASTNONBLANK函数	236
使用期初和期末余额	241
理解高级时间智能计算	245
理解累计至今区间	246
理解DATEADD函数	249
理解FIRSTDATE、LASTDATE、FIRSTNONBLANK和
LASTNONBLANK函数	255
利用时间智能函数进行钻取	258
使用自定义日期表	258
基于周的时间智能	259
自定义YTD、QTD和MTD	262
结论	264

第9章  计算组	265
计算组介绍	265
创建计算组	268
理解计算组	274
理解计算项的应用	277
理解计算组优先级	285
在计算项中包含或排除度量值	289
理解横向递归	292
使用最佳实践	296
结论	296

第10章  使用筛选上下文	298
使用HASONEVALUE和SELECTEDVALUE函数	299
ISFILTERED和ISCROSSFILTERED函数介绍	303
理解VALUES和FILTERS函数的区别	306
理解ALLEXCEPT和ALL/VALUES函数的区别	308
使用ALL函数避免上下文转换	312
使用ISEMPTY函数	314
数据沿袭和TREATAS函数介绍	316
使用固化筛选器	320
结论	326

第11章  处理层级结构	328
计算层级占比	328
处理父/子层级结构	333
结论	344

第12章  使用表函数	345
使用CALCULATETABLE函数	345
操作表的函数	347
使用ADDCOLUMNS函数	348
使用SUMMARIZE函数	351
使用CROSSJOIN函数	354
使用UNION函数	356
使用INTERSECT函数	360
使用EXCEPT函数	361
使用表作为筛选器	363
实现或（OR）条件	364
将销售额的计算范围缩小至首年客户	367
计算新客户	368
使用DETAILROWS函数复用表表达式	370
创建计算表	372
使用SELECTCOLUMNS函数	372
使用ROW函数创建静态表	373
使用DATATABLE函数创建静态表	374
使用GENERATESERIES函数	375
结论	376

第13章  编写查询	377
DAX Studio介绍	377
理解EVALUATE函数	378
EVALUATE函数语法介绍	378
在DEFINE函数中使用VAR	379
在DEFINE函数中使用度量值  	381
实现DAX查询的常用模式	382
使用ROW函数测试度量值	382
使用SUMMARIZE函数	383
使用SUMMARIZECOLUMNS函数	385
使用TOPN函数	391
使用GENERATE和GENERATEALL函数	396
使用ISONORAFTER函数	399
使用ADDMISSINGITEMS函数	401
使用TOPNSKIP函数	402
使用GROUPBY函数	402
使用NATURALINNERJOIN和NATURALLEFTOUTERJOIN函数	405
使用SUBSTITUTEWITHINDEX函数	407
使用SAMPLE函数	409
理解DAX查询中的自动匹配（Auto-Exists）行为	410
结论	416

第14章  高级DAX原理	418
扩展表介绍	418
理解RELATED函数	422
在计算列中使用RELATED函数	424
理解表筛选器和列筛选器的区别	425
在度量值中使用表筛选器	428
理解活动关系	431
表的扩展行为和筛选行为的区别	433
扩展表中的上下文转换	435
理解ALLSELECTED函数和影子筛选上下文	436
影子筛选上下文介绍	437
ALLSELECTED函数返回迭代的行	441
无参数的ALLSELECTED函数	443
ALL系列函数	443
ALL函数	445
ALLEXCEPT函数	446
ALLNOBLANKROW函数	446
ALLSELECTED函数	446
ALLCROSSFILTERED函数	446
理解数据沿袭	446
结论	449

第15章  高级关系	451
使用计算列创建物理关系	451
创建基于多列的关系	451
创建基于范围的关系	453
使用计算列创建关系中的循环依赖问题	456
使用虚拟关系	459
在DAX中转移筛选器	460
使用TREATAS函数转移筛选器	462
使用INTERSECT函数转移筛选器	463
使用FILTER函数转移筛选器	464
使用虚拟关系实现动态分组	465
理解DAX中的物理关系	468
使用双向交叉筛选器	470
理解一对多关系	472
理解一对一关系	473
理解多对多关系	473
通过桥接表实现多对多关系	473
通过公共维度表实现多对多关系	479
使用MMR弱关系实现多对多关系	483
选择正确的关系类型	485
管理数据颗粒度	486
管理关系中的歧义	490
理解活动关系中的歧义	492
解决非活动关系中的歧义	494
结论	496

第16章  DAX中的高级计算	497
计算两个日期之间的工作日数量	497
同时展示预算数据和销售数据	505
计算同店销售额	508
对事件进行排序	514
根据最新销售日期计算上一年的销售额	517
结论	522

第17章  DAX引擎	523
了解DAX引擎的架构	523
公式引擎介绍	524
存储引擎介绍	525
VertiPaq（in-memory）存储引擎介绍	526
DirectQuery存储引擎介绍	527
理解数据刷新	527
理解VertiPaq存储引擎	528
列式数据库介绍	528
理解VertiPaq压缩	531
理解值编码	531
理解哈希编码	532
理解行程长度编码（RLE）	533
理解再编码	536
确定最佳排序顺序	536
理解层级和关系	538
理解分段和分区	539
使用动态管理视图	540
理解关系在VertiPaq中的运用	542
物化介绍	545
聚合表介绍	547
为VertiPaq配置合适的硬件	549
是否可以自主选择硬件	550
设置硬件优先级	550
CPU型号	550
内存速度	552
内核数量	552
内存大小	552
硬盘I/O和分页	553
硬件选择的最佳实践	553
结论	553

第18章  优化VertiPaq引擎	555
收集有关数据模型的信息	555
反规范化	560
列基数	566
处理日期和时间列	567
计算列	570
使用布尔类型的计算列优化复杂筛选器	572
计算列的处理	573
存储合适的列	574
优化列存储	577
列的拆分优化	577
优化大基数列	578
禁用属性层级结构	578
优化钻取属性	579
管理VertiPaq聚合表	579
结论	582

第19章  分析DAX查询计划	583
捕获DAX查询	583
DAX查询计划介绍	586
收集查询计划	587
逻辑查询计划介绍	587
物理查询计划介绍	588
存储引擎查询介绍	589
获取配置信息	590
使用DAX Studio	591
使用 SQL Server Profiler	594
读懂VertiPaq存储引擎查询	597
xmSQL语法介绍	597
聚合函数	598
算术运算	600
筛选运算	600
Join运算符	602
批处理事件中的临时表和浅关系	603
理解扫描时间	605
理解DISTINCTCOUNT函数的内部行为	606
理解并行度和数据缓存	607
理解VertiPaq缓存	609
理解CallbackDataID函数	611
读懂DirectQuery模式下的存储引擎查询	616
分析复合模型	617
在数据模型中使用聚合表	618
读懂查询计划	620
结论	626

第20章  DAX优化	628
定义优化策略	629
确定要优化的单个DAX表达式	629
创建查询副本	632
创建DAX查询副本	632
使用DAX Studio创建查询度量值	633
创建MDX查询副本	635
分析执行时间和查询计划信息	636
发现存储引擎或公式引擎中的性能瓶颈	639
修改并重新运行测试查询	639
优化DAX表达式中的瓶颈	639
优化筛选条件	640
优化上下文转换	644
优化IF条件	650
优化度量值中的IF函数	650
选择IF函数还是DIVIDE函数	655
优化迭代函数中的IF函数	658
减少CallbackDataID函数带来的影响	661
优化嵌套的迭代函数	665
避免在表筛选器中使用DISTINCTCOUNT函数	671
使用变量避免重复计算	676
结语结论	681