DAX查詢進階：駕駛DAX Studio

01-25

沒有愛上車是因為還沒學會駕駛。

前情回顧

在上一篇DAX查詢入門：DAX Studio介紹中，我們已經初步了解了DAX Stuido。

它提供的能力包括:

DAX查詢編寫
DAX查詢/DAX代碼格式化
數據模型元數據查看
當前DAX引擎支持函數查看
當前DAX引擎DMV
DAX查詢結果查看
DAX查詢結果數據導出
DAX引擎運行監控（為代碼優化做準備）
DAX引擎時間跟蹤
DAX代碼性能優化
我們初步了解了:
Excel透視表可以用等效的DAX查詢表示
DAX查詢可以表示出任意複雜的查詢結果，這種能力遠遠超過Excel透視表本身
DAX Studio是編寫DAX查詢的強大武器
DAX Studio除了編寫DAX查詢外，還具備其他更多特性
理解如何藉助DAX Studio完成DAX查詢與Excel的連接
理解如何直接在原生Excel中實現DAX查詢
理解如何在Power BI Desktop中進行DAX查詢
理解如何藉助DAX Studio測試DAX查詢（返回表或返回值）

本文將詳細說明暢快駕駛DAX Studio的方法。

現在打開你的DAX Studio以及示例文件Contoso.pbix(你可以使用自己的測試文件)。如下:

DAX查詢編寫

DAX查詢的語法為:

根據微軟官方DAX查詢參考可知:

DAX 語言提供了一種新語法，可從查詢中返回表數據。

通過 DAX 查詢，用戶可從內存分析引擎 (VertiPaq) 來檢索由表表達式定義的數據。用戶可將度量值作為查詢的一部分創建；查詢結束後，這些結果會被釋放，除非導出這些結果。

DAX 查詢的語法如下:

[DEFINE { MEASURE <tableName>[<name>] = <expression> }EVALUATE <table>[ORDER BY {<expression> [{ASC | DESC}]}[, …][START AT {<value>|<parameter>} [, …]]]

具體參考微軟官方文檔，不做過多說明。

其中，EVALUATE是重點。包含用於生成查詢結果的表表達式。表達式可以使用任何定義的度量值。表達式必須返回表。如果需要標量值，則度量值的作者可以將其標量包裝在 ROW() 函數內，以便生成包含所需標量的表。

在DAX中，常見的返回表的情況包括:

直接返回表本身，如:

EVALUATE Product
返回被過濾的表，如:
EVALUATEFILTER ( "Product", "Product"[Color] = "red" )
返回CACULATETABLE計算後的表，如:
EVALUATECALCULATETABLE ( DISTINCT ( "Product"[Product Name] ), "Product Category"[Category] = "audio")
ALL和VALUES也是返回表的函數，在DAX Studio中可以很好的看到它們的特性，如:

EVALUATEALL("Product Category"[Category])EVALUATEVALUES("Product Category"[Category])

結果如下:

注意:DAX Studio 2.6 開始支持返回多結果，也就是一次性寫多個EVALUATE進行計算。

另外一個結果是:

發現了嗎？ALL和VALUES在這種情況下返回的結果是一樣的。你還可以自行實驗有重複值列的情況。

流暢的代碼編寫體驗

之所以說當你熟悉了DAX的感覺後會不能再沒有她，正是因為她能幫助你流暢地編寫DAX代碼。這表現在:

DAX 函數及模型元數據智能感知，如:

DAX Studio 可以自動識別當前DAX引擎可用的DAX函數以及當前模型的元數據(表及列)並迅速進行提示，這使得編寫代碼的速度很快。

DAX 代碼著色及格式化
DAX Studio 使用http://DAXFormatter.com對代碼進行格式化，如下:

點擊:

得到:

在編寫複雜的 DAX 查詢時，代碼著色以及括弧匹配能幫助我們避免很多錯誤。

語法檢測及錯誤提醒
如果你的代碼出現錯誤，DAX Studio可以進行語法檢查並幫助排除錯誤。例如在上述的DAX查詢中，對[Category Code]不小寫多寫了一個空格成為[Category Code ]，這也是不允許的，DAX Studio會報錯，如下:

DAX Studio 幫助鎖定了出錯的位置是第4行第17列，並提示了錯誤信息「找不到列Category Code 或該列不能用於此表達式」。

DAX 查詢編寫最佳實踐

基於DAX查詢具有的特點:DAX查詢是嵌套進行的。可以逐層地來構造查詢，以確保在每一步都可以進行調試。

注意利用 DAX Studio 2.6 版本以後可返回多結果的功能。

例如:構建一個返回分類及子分類下銷售額匯總的表。這個在DAX Studio中逐步編寫代碼的過程大致如下:

DEFINE //定義度量值計算銷售額 MEASURE Sales[Total Sales] = SUMX ( Sales, Sales[Net Price] * Sales[Quantity] )EVALUATE//總銷售額ROW ( "values", [Total Sales] )EVALUATE//生成類別層級表SELECTCOLUMNS ( GENERATE ( "Product Category", RELATEDTABLE ( "Product Subcategory" ) ), "Category", "Product Category"[Category], "Sub Category", "Product Subcategory"[Subcategory])EVALUATE//為類別層級表添加銷量ADDCOLUMNS ( SELECTCOLUMNS ( GENERATE ( "Product Category", RELATEDTABLE ( "Product Subcategory" ) ), "Category", "Product Category"[Category], "Sub Category", "Product Subcategory"[Subcategory] ), "Total Sales", [Total Sales])

可以看出在上述過程中，並沒有刪除每一步的代碼，而總是利用每一次的代碼繼續下一步。並得到結果:

可以發現，有的類別層級組合是沒有銷售額的，不希望顯示這樣的結果，於是可以進一步用FILTER來進行過濾，甚至進一步格式化輸出結果，如下:

EVALUATE//為類別層級表添加銷量並過濾空行//格式化輸出結果FILTER ( ADDCOLUMNS ( SELECTCOLUMNS ( GENERATE ( "Product Category", RELATEDTABLE ( "Product Subcategory" ) ), "Category", "Product Category"[Category], "Sub Category", "Product Subcategory"[Subcategory] ), "Total Sales", FORMAT( [Total Sales] / 10000 , "0.0W" ) ), ISBLANK ( [Total Sales] ) = FALSE () && [Total Sales] <> "")

結果為:

這已經很完美。

尤其是在編寫複雜的 DAX 查詢時，可以採用逐步測試的方法並配合注釋使得DAX代碼可以被長期維護。

使用DAX Studio理解模型中的元數據

DAX Studio可以幫助分析師了解模型的元數據，如下:

包括Power BI Desktop自動生成的代碼，例如:Power BI Desktop會自動為每個日期列添加一個適用於時間智能的隱藏日期表。這個隱藏的日期表在Power BI Desktop默認是看不到的，但是在DAX Studio中卻一覽無遺。

如果禁用Power BI Desktop自動生成日期表的功能，如下:

此時回到DAX Studio中查看模型的元數據將不再存在隱藏的日期表，如下:

使用DAX Studio理解所有DAX函數

DAX Studio可以讀取DAX引擎支持的所有函數，如下:

這可以幫助分析師快速了解所有的DAX函數，包括DAX引擎更新後可能新加入的函數。

使用DAX Studio初步理解DMV並使用Power BI Desktop作為分析服務

Analysis Services Dynamic Management Views (DMV)，即:分析服務的動態管理視圖，它提供了對當前運行的分析服務動態信息進行查詢的介面。如下:

由於Excel Power Pivot，Power BI Desktop以及SSAS均使用分析服務引擎，所以都具備這個DMV。通過查詢DMV，用戶可以知道由於當前分析服務的幾乎所有信息，這為基於Power BI Desktop的高級應用提供了基礎。

例如:DMV透露了作為當前分析服務的實例，那便可以使用該實例作為伺服器。將Excel作為客戶端，與之進行連接。

這裡顯示了埠號:

用Excel作為客戶端工具與之連接，如下:

輸入剛剛在DAX Studio中得到的連接信息，如下:

Excel提示可以與當前的分析服務連接，如下:

此時，在Power BI Desktop定義的模型便可以直接在Excel中使用了，如下:

至此，Excel與Power BI Desktop完全連接起來，不需要在Excel中建立數據模型一樣可以直接對數據模型加以利用。

有關DMV的深度利用，超出了本文範圍，後續再做描述。

使用DAX Studio輸出DAX查詢

這又是一項DAX Studio非常出彩的功能。我們知道在Excel中單表限制是100W行數據；而在Power BI Desktop中又無法導出數據。這就存在一個問題，那就是:是否可能將載入進數據模型(Excel 數據模型或Power BI Desktop數據模型)的大數據量級事實表(如:超過1000W行)導出？

對於分析師而言，這是一個非常重要的功能。這項功能也是由DAX Studio提供的。

在示例PBI文件中便使用了多達1200W行的銷售數據，如下:

EVALUATEROW ( "rows of sales", COUNTROWS ( Sales ) )

結果: