DAX權威指南：運用Power BI、SQL Server Analysis Services和Excel實現商業智能分析(第2版)（簡體書）

前言

我們已經創作了大量關於DAX的內容，比如那些關於Power Pivot和SSAS表格模型的書、博客、文章、白皮書，以及一本關於DAX Patterns的著作。還有必要再為DAX另外寫一本書嗎？這門語言值得花這麼多精力嗎？當然，我們認為答案是肯定的。

當一本書誕生時，編輯首先關心的是它的頁數。這一點之所以重要，是因為頁數涉及定價、管理和資源的分配等。這多少讓作者有一點沮喪。事實上，每次寫書時，我們都需要謹慎分配關於產品（無論是Excel Power Pivot還是SSAS表格模型）和DAX語言描述所占的資源。沒有足夠的篇幅讓我們對DAX暢所欲言的感覺是痛苦的。畢竟，你不可能寫1000頁關於Power Pivot的內容，這種體量的書對任何人來說都是一場噩夢。

因此，這些年來，我們在撰寫關於Power Pivot和SSAS表格模型的書的同時，也編寫了一本系統闡述DAX的書，只不過它暫時被封存。現在，我們決定打開封印，不再糾結摘錄哪一部分內容——我們將介紹關於DAX的所有細節，不做任何刪減，這就是本書的由來。

本書不介紹如何創建計算列，不告訴你在哪個對話框中可以設置哪些屬性。因為這不是一本手把手教你使用Visual Studio、Power BI和Power Pivot的書。相反，它將深入介紹DAX語言，從入門開始一直到非常技術化的細節，來指導你如何優化自己的代碼和模型。

在創作過程中，我們對它的每一頁都愛不釋手，無數次回顧這些內容，以至於可以倒背如流。每當我們認為有重要的內容要補充時，就會繼續添加內容。這讓本書的頁數只增不減，因為每一頁都是有價值的。在此期間，我們加深了對DAX的理解並且享受整個創作過程。

但還有一件事情需要說明，為什麼你需要這樣一本關於DAX的書？

在你第一次使用Power Pivot或Power BI之後可能就有了這種想法。拜托，你不是一個人，我們第一次使用之後也產生了這個困惑。DAX看起來太簡單了！它和Excel函數是如此相似！而且，如果你學習過其他編程語言或查詢語言，那麼你可能已經習慣參照語法實例、套用已經掌握的思維模式來學習一門新的語言。這些都是我們在學習DAX過程中犯過的錯誤，希望你不要重蹈覆轍。

DAX是一門強大的語言，支持這門語言的分析工具正在不斷增加。但在強大的背後，它的一些原理很難通過歸納法（從具體實例推導出普遍規律的一種方法）來理解。例如，對計值上下文（Evaluation Context）的理解需要用到演繹推理：先接觸理論本身，再通過案例加深對理論的理解。我們知道很多人都不習慣這種學習方式，他們更喜歡在實踐中學習，即先研究如何解決具體問題，再通過不斷的練習和積累，歸納出公式背後的原理。如果你也希望使用這種方式學習，那麼本書可能不適合你。我們另有一本書：DAX Patterns，它是一本面向實戰的案例集，不包含公式原理的解釋，也不會告訴你為什麼公式寫成這樣效率更高，它適合讓你以直接套用的方式解決實際問題。但本書的目的是讓你真正掌握DAX。基於此，本書中所有的案例都是為了解釋DAX語言的行為，而非解決特定問題。如果你發現書中的某些公式恰好可以應用到你的模型裡，那麼恭喜你。但請記住，這只是意外之喜，並非案例的本意。最後，請務必留意書中的注解，以確保理解案例中的代碼所包含的陷阱。出於講解的目的，書中使用的公式源碼通常並非最佳實踐。

希望你能和我們一起享受這段學習DAX的美妙旅程，就像當初我們寫這本書時那樣。

本書受眾

如果你只是偶爾會使用DAX，那麼本書或許不是最佳選擇。市面上已經有很多書提供了DAX相關工具和語言本身的簡明介紹，它們可以幫助你從零開始達到基礎級別的DAX編程水平。我們對此非常了解，因為我們也寫過類似的書。

而如果你是抱著認真的態度學習DAX，想要了解這門優美語言的所有細節，那麼本書就是為你準備的。如果這是你學習DAX的第一本書，那麼請不要期望太早領悟那些高級概念。建議你首先通讀全書，等到積累一些經驗後再重讀那些最復雜的章節，那時你會對它們有更清晰的了解。

不同職位、不同目的的人都可以從DAX中獲益，Excel用戶可以利用DAX操控Power Pivot數據模型，商業智能（BI）專家需要在不同規模的BI解決方案中編寫DAX代碼，非專業Power BI使用者需要在自助BI模型中編寫DAX公式。不同的人群在本書中都能找到適合自己的內容。某些內容（特別是性能優化部分）更適合BI專家，因為用於優化DAX度量值的知識具備比較高的技術性；但我們認為Excel用戶同樣需要了解DAX表達式之間的性能差異，以便獲得最佳的模型運行效果。

最後，你需要不斷從本書中學習，而不能僅僅停留在閱讀階段。我們保持入門部分的內容的通俗易懂，並遵循從零開始學DAX的邏輯路徑。然而，當其中涉及的概念開始變得復雜時，我們將不再試圖簡化它，而是回歸真實。DAX不是一門簡單的語言，掌握它和理解引擎的每處細節花費了我們多年的時間，不要奢望隨手翻翻本書就能明白全部內容。你在閱讀本書時要高度集中注意力。作為回報，我們提供了關於DAX各個方面的內容，且這些內容是前所未有的，以助你成為一名真正的DAX專家。

準備知識

我們希望本書讀者在Power BI和數據分析方面具備一定的基礎知識，如果你對DAX有過一些接觸，這對你有好處，它能幫助你更快地閱讀本書第一部分內容。當然，了解DAX不是必需的。

本書部分內容引用了MDX和SQL代碼，但你不必預先了解這些語言，因為它們只是反映不同表達式之間的區別，即使你不了解這些代碼也沒有關係。這意味著那些特定主題不是為你準備的。

在本書最復雜的高級內容部分，我們介紹了並行計算、內存訪問、CPU使用，以及其他過於高深、讓大部分讀者可能感到陌生的知識。開發人員熟悉這些內容，而Excel高級用戶可能有點畏懼。不過，當涉及優化內容時，這些信息是必需的。因此，本書最復雜的部分面向BI開發人員而不是Power BI和Excel用戶。然而，我們相信每個閱讀過它的人都會從中受益。

本書編排

本書在內容設計上遵循合理的邏輯順序，從入門內容逐步深入到復雜內容。每章的內容在撰寫時都基於之前內容被完全理解的基礎之上；幾乎不會重復前面介紹過的概念。正因如此，我們強烈建議讀者在首次閱讀本書時采用從頭至尾的方式系統學習，不要提前跳到復雜章節。

讀過一次之後，它就可以成為你學習DAX的參考書。例如，如果你對ALLSELECTED函數的原理不是很明白，則可以直接翻到講解這個函數的那章打消疑問。不過，在缺少對之前內容理解的情況下，閱讀該部分可能無法達到預期效果，甚至會遇到更糟糕的情況：產生片面或錯誤的理解。

以下是各章節內容概覽。

? 第1章是對DAX的簡要介紹，其中有些內容是專門針對已經有其他編程語言基礎的用戶，比如SQL、Excel或MDX。本章沒有引入任何新概念，只給出你可能已經知道的，一些關於DAX和其他語言差異的提示。

? 第2章介紹DAX語言的概況，涵蓋計算列、度量值、錯誤處理函數等基本概念，並列出DAX的大部分基礎函數。

? 第3章專門介紹基礎表函數。DAX的許多函數都使用表作為參數或返回表作為結果。本章內容涵蓋最基本的表函數。高級表函數將在第12章和第13章中介紹。

? 第4章專門介紹計值上下文。計值上下文是整個DAX語言的基石，本章連同第5章，是整本書最重要的內容。

? 第5章只介紹兩個函數：CALCULATE和CALCULATETABLE。它們是DAX中最重要的函數，是否理解計值上下文將直接決定你能否用好這兩個函數。

? 第6章介紹變量。在本書的案例中使用了大量的變量，本章專門介紹它們的語法並解釋如何使用變量。當你在接下來的章節中看到大量使用變量的例子時，本章內容將是非常有參考性的。

? 第7章介紹迭代器和CALCULATE函數：二者是“天作之合”。學習如何使用迭代器，以及上下文轉換，可以充分發揮DAX的強大功能。本章中會介紹幾個有助於理解如何利用這些工具的例子。

? 第8章深入闡述時間智能計算，包括年初至今、月初至今、去年同期、基於周的時間段和自定義日期表等常用計算。

? 第9章專門介紹DAX中引入的最新特性：計算組（Calculation Groups）。計算組是非常強大的建模工具。本章介紹如何創建和使用計算組，以及計算組的基本概念，並展示了一些示例。

? 第10章介紹篩選上下文的更高級用法，比如數據沿襲、對篩選上下文的檢查及編寫高級公式的有用工具。

? 第11章介紹如何對層級結構執行計算，以及如何使用DAX處理父/子結構。

? 第12章和第13章介紹對編寫查詢和/或執行高級計算都很有用的高級表函數。

? 第14章進一步介紹計值上下文的知識，並在擴展表理論的幫助下介紹諸如ALLSELECTED和KEEPFILTERS之類的復雜函數。本章內容屬於高級內容，揭示了復雜DAX表達式的大部分秘密。

? 第15章介紹如何管理DAX中的關係。實際上，借助DAX，任何類型的關係都可以在數據模型中被設置。本章介紹分析型數據模型中常見的多種類型的關係。

? 第16章介紹幾個用DAX解決的復雜計算案例。這是關於DAX語言的最後一章，本章有助於讀者發現解決方案和產生新的想法。

? 第17章詳細描述VertiPaq引擎，它是運行DAX模型時最常用的存儲引擎。了解這一點，對於學習如何在DAX公式中獲得最佳性能至關重要。

? 第18章基於第17章的內容，介紹數據模型級別可用的優化手段。讀者會在本章學到何時規範化模型，如何減少列基數，如何設置關係和使用最少的內存以在DAX中獲得最佳性能。

? 第19章介紹如何讀懂查詢計劃，以及如何借助DAX Studio和SQL Server Profiler等工具來評估DAX查詢的性能。

? 第20章基於之前章節介紹的優化知識，介紹一些技術解決方案，本章首先展示示例公式，然後評估它們的性能，最後解釋並給出優化後的公式。

目 錄


第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