2.3 Power Pivot數據模型管理界面

2.3.1 從Pivot Table到Power Pivot

在外觀上，Power Pivot超級數據透視表與傳統Excel數據透視表（Pivot Table）幾乎沒什么區別，甚至連創建過程都基本類似，那為什么有些人做出來的就是普通的傳統Excel數據透視表，有些人做出來的卻是功能強大的Power Pivot超級數據透視表呢？秘訣就在于在“創建數據透視表”對話框中的一個關鍵設置：勾選“將此數據添加到數據模型”復選框。

創建數據透視表的過程如下：選擇數據源，依次選擇“數據”→“插入”→“數據透視表”命令，彈出“創建數據透視表”對話框，如下圖所示。在這個對話框下方有一個“將此數據添加到數據模型”復選框，在默認情況下，這個復選框是不被勾選的。在不勾選“將此數據添加到數據模型”復選框的情況下，單擊“創建數據透視表”對話框中的“確定”按鈕，生成的是普通的、功能有限的傳統Excel數據透視表；在勾選“將此數據添加到數據模型”復選框的情況下，單擊“創建數據透視表”對話框中的“確定”按鈕，生成的是Power Pivot超級數據透視表。

盡管在外觀上，Power Pivot超級數據透視表和傳統Excel數據透視表并沒有明顯的區別，但是，由于勾選了“將此數據添加到數據模型”復選框，因此Power Pivot超級數據透視表與傳統Excel數據透視表的內在生成方式完全不同，二者的能力也有天壤之別。

在下圖中，在Power Pivot超級數據透視表值區域中計算的是圖書銷售冊數的“非重復計數”，而非銷售總冊數的和。在傳統Excel數據透視表中是沒有“非重復計數”功能的，而在Power Pivot超級數據透視表中，實現“非重復計數”功能只是牛刀小試。

回到本節的主題，為什么在勾選了“創建數據透視表”對話框中的“將此數據添加到數據模型”復選框后，會使數據透視表忽然具有如此神奇的功能呢？這是因為在勾選“將此數據添加到數據模型”復選框后，Excel會將數據源中的數據先加載到Power Pivot專用數據庫中，使我們可以在將數據展示到數據透視表之前，利用Power Pivot數據模型管理界面對數據進行各種自定義的靈活操作。

如果要查看Power Pivot背后的數據模型（Power Pivot專用數據庫），那么選擇“Power Pivot”→“數據模型”命令，進入Power Pivot數據模型管理界面，如下圖所示。

注意：如果Excel界面上沒有Power Pivot菜單項，則需要在Excel中進行如下設置：選擇“文件”→“選項”命令，在彈出的“Excel選項”對話框中選擇“加載項”選項，在“管理”下拉列表中選擇“COM加載項”選項，如下圖所示。然后單擊旁邊的“轉到”按鈕，在彈出的“COM加載項”對話框中勾選“Microsoft Power Pivot for Excel”復選框，最后單擊“確定”按鈕。

Power Pivot數據模型管理界面分成上、中、下三部分，上面部分是菜單區，中間部分是剛剛導入模型中的數據，下面部分的單元格是DAX表達式編輯區，即我們寫入各種超級強大的DAX表達式的地方，如下圖所示。

在Power Pivot數據模型管理界面左下角，可以看到剛剛導入數據的表被Power Pivot數據模型管理界面自動命名為“區域”，而不是Excel工作表的默認名稱，如果不喜歡這個名稱，那么我們可以對它重命名，但此處我們使用這個默認名稱。

在Power Pivot數據模型管理界面，我們可以對來自數據源的數據進行初步觀察和研究。

在數據模型管理界面的數據表中，我們可以對數據進行篩選和增加新列等操作，增加的新列在Power Pivot的術語中稱為計算列。

在DAX表達式編輯區中，我們可以編寫各種實現復雜數據分析運算的DAX表達式。Power Pivot數據模型管理界面相當于Power Pivot數據模型的控制面板，是我們調取Power Pivot強大功能的地方。

需要注意的是，在Power Pivot數據模型管理界面中的篩選操作結果并不會傳遞到由此生成的Power Pivot超級數據透視表中。Power Pivot數據模型管理界面中的篩選操作只是用于觀察后臺數據和初步調試DAX表達式的，與由此生成的Power Pivot超級數據透視表沒有任何聯系。

2.3.2 Power Pivot中的自定義運算方法

我們先從一個最簡單的DAX表達式開始：利用Power Pivot和DAX表達式實現傳統Excel數據透視表中已有的一個功能，即利用DAX中的SUM（）函數對圖書銷售冊數進行匯總。

首先創建一個Power Pivot超級數據透視表，注意在“創建數據透視表”對話框中勾選“將此數據添加到數據模型”復選框；然后選擇“Power Pivot”→“數據模型”命令，進入Power Pivot數據模型管理界面，我們看到，數據已經加載到Power Pivot數據模型管理界面中了。注意，如果在創建數據透視表時沒有勾選“將此數據添加到數據模型”復選框，那么在Power Pivot數據模型管理界面中是看不到任何數據的。

Power Pivot數據模型管理界面中的數據和數據源是動態鏈接的，也就是說，如果數據源中的數據發生變化，那么在單擊Power Pivot數據模型管理界面菜單欄中的“刷新”按鈕后，Power Pivot數據模型管理界面中的數據也會跟著發生變化。

接下來在DAX表達式編輯區中任意一個單元格中輸入如下DAX表達式：

上述操作如下圖所示。

觀察發現，在輸入“=SUM（”之后的左單引號（'）時，Power Pivot會提示數據模型中可用的字段名稱，這大大提高了輸入DAX表達式的速度，大家要充分利用這個特性。

在輸入DAX表達式后，Power Pivot會自動給DAX表達式起一個默認名稱“度量值1”。這里需要大家記住：在Power Pivot中，將寫在Power Pivot數據模型管理界面下方單元格中、起匯總計算作用的DAX表達式稱為度量值表達式或度量值。度量值表達式存儲于Power Pivot超級數據透視表值區域中。我們可以自行定義度量值表達式的名稱（DAX表達式冒號前面的部分），為了容易理解，我們將“度量值1”改成“my總冊數”。

憑借對Excel工作表函數的使用經驗，我們推測DAX表達式“my總冊數”的作用是對數據源中的“T3銷售冊數”字段進行累加操作，相當于在數據透視表中增加了一個新的“值”字段（可以拖曳至Power Pivot超級數據透視表值區域中的字段）。下面，我們來看看將這個DAX表達式添加到Power Pivot超級數據透視表值區域中的效果。單擊Power Pivot數據模型管理界面菜單欄中的“數據透視表”按鈕，在彈出的下拉列表中選擇“數據透視表”命令，如下圖所示。

現在我們進入了Power Pivot超級數據透視表界面。在“數據透視表字段”視圖中拖曳字段，生成如下圖所示的數據透視表。此時，在“數據透視表字段”視圖中，可以看到一個新的字段“my總冊數”，這就是剛才在Power Pivot數據模型管理界面中設計的DAX表達式。該DAX表達式字段現在已經被拖曳至Power Pivot超級數據透視表值區域中了。

我們發現，數據透視表值區域中的數值和直接將數據源中的“T3銷售冊數”字段拖曳至數據透視表值區域中的默認求和結果一致。

注意，雖然這只是一個最簡單的度量值表達式，但是它留給我們的想象空間卻是巨大的。在傳統Excel數據透視表中，值區域中的匯總方式只能在已有的幾種方式中選擇，而在Power Pivot超級數據透視表中，我們可以用DAX函數自定義設計數據匯總方式。

2.3.3 Power Pivot中的度量值表達式與計算列公式

前面已經介紹過，Power Pivot數據模型管理界面下面部分的單元格是DAX表達式編輯區，我們將寫在這里的DAX表達式稱為度量值表達式。可以將度量值表達式簡單地理解為“一種自定義的數據透視表值區域匯總方式”。

當Power Pivot的數據源為單個表時，度量值表達式可以寫在DAX表達式編輯區中的任意單元格中，度量值表達式與其所在單元格的位置無關，只需度量值表達式引用了數據源中正確的字段。

在傳統Excel數據透視表中，如果數據源中的某個字段是數字類型，那么當我們將這個字段拖曳至數據透視表值區域中時，默認的匯總方式是求和。在Power Pivot超級數據透視表中，我們可以設計DAX表達式模擬傳統數據透視表的這個行為。

在Power Pivot數據模型管理界面中，除了可以在DAX表達式編輯區中輸入度量值表達式，還可以在Power Pivot數據模型管理界面中的數據表最右側添加新的計算列。

在Power Pivot中，我們將Power Pivot數據模型管理界面中的數據表中新增加的列稱為計算列。計算列一般用于Power Pivot超級數據透視表篩選環境中（如行標題、列標題、切片器、篩選字段等）。

在下圖中，我們為數據表添加了一個新的計算列，用于生成圖書大類代碼。具體操作方法為，在數據表最后的空白列中任選一個單元格，輸入計算列公式“=LEFT（'區域'[T0大類K]，1）”，按Enter鍵即可得到結果。這個DAX函數和Excel工作表函數的名稱和用法基本相同。在DAX中，很多簡單函數與Excel工作表函數的名稱和用法基本相同。計算列的默認名稱一般是“計算列+序號”，為了使計算列的名稱更加友好，可以雙擊列名，將默認列名重命名為自己喜歡的名稱，如這里將計算列的名稱重命名為“大類代碼”。

2.3.4 最重要的函數——CALCULATE（）函數

前面介紹了在Power Pivot超級數據透視表值區域中自定義匯總方式的基本方法，即設計DAX表達式。接下來，我們用DAX表達式實現更加靈活的Power Pivot超級數據透視表值區域匯總方式。利用這種方式，我們可以在Power Pivot超級數據透視表值區域中實現大部分復雜的匯總分析。下面我們隆重介紹一個非常重要的DAX函數——CALCULATE（）函數。

我們知道，數據透視表值區域中每個單元格中的內容雖然看起來只是一個簡單的數值，但每個數值背后，都對應著一個由該單元格所處的數據透視表篩選環境下的數據源子集，該數值便是在該數據源子集的基礎上匯總計算而來的。換句話說，數據透視表值區域中每個單元格中的數值，都是由它對應的數據源子集經過某種特定的匯總計算的結果。

由此我們推想：如果有一種方法能根據實際需求修改數據透視表值區域中的單元格所對應的數據透視表篩選環境，從而得到數據源的任意子集的匯總值，那么我們還有什么做不到的事情？DAX就是用于做這個的。而在DAX中，最重要的函數就是CALCULATE（）函數。

CALCULATE（）函數是一個匯總計算函數，該函數的特別之處在于，CALCULATE（）函數在執行匯總計算之前，不僅能夠識別其當前所處的數據透視表篩選環境，還能對其所處的數據透視表篩選環境進行修改。

事實上，CALCULATE（）函數的匯總計算是在新的“疊加的數據透視表篩選環境”下進行的。該函數通常使用兩個參數，第一個參數為“自定義的匯總計算”，第二個參數為“對當前篩選環境的修改”，其語法格式如下：

CALCULATE（）函數的語法看起來并不復雜，但是，這里需要強調一點，那就是CALCUCATE（）函數的內部運算邏輯：先對當前數據透視表篩選環境進行修改，再執行自定義的匯總計算。之所以強調CALCULATE（）函數的內部運算邏輯，是因為它的內部運算邏輯與參數的出場順序是相反的，這與我們熟知的Excel工作表函數的工作方式完全不同，大家一定要留意。在CALCULATE（）函數進行匯總計算時，先從第二個參數開始。在CALCULATE（）函數中，第二個參數稱為篩選器參數，作用是修改CALCULATE（）函數所處的當前數據透視表篩選環境；第一個參數稱為匯總參數，作用是進行匯總計算。

CALCULATE（）函數中的篩選器參數可以省略。在只有匯總參數的情況下，CALCULATE（）函數默認接受當前數據透視表篩選環境，也就是說，如果將CALCULATE（）函數用于數據透視表值區域中，那么CALCULATE（）函數會在其當前所處的數據透視表篩選環境下進行匯總計算。

我們從最簡單的情況開始，研究只有匯總參數的CALCULATE（）函數的應用。例如，在前面介紹的DAX表達式“my總冊數”外面包裹一個CALCULATE（）函數，并且將新的DAX表達式命名為“總冊數CALCU”，如下圖所示。

返回Power Pivot超級數據透視表界面，將新設計的DAX表達式拖曳至數據透視表值區域中，計算結果如下圖所示。我們看到，以下兩個DAX表達式的計算結果并沒有什么不同。

上述DAX表達式的計算結果之所以完全相同，是因為在Power Pivot中有如下特性：

當一個DAX表達式被拖曳至數據透視表值區域中時，會隱含地在該DAX表達式外面包裹一個CALCULATE（）函數。

因此DAX表達式：

在被拖曳至數據透視表值區域中時，會隱含地在該DAX表達式外面包裹一個CALCULATE（）函數，變成：

按照這個規律，我們設計的新的DAX表達式：

在被拖曳至數據透視表值區域中時，也會隱含地包裹一個CALCULATE（）函數，變成：

其效果相當于只包裹了一個CALCULATE（）函數。因此，這兩個DAX表達式得到了相同的計算結果。

事實上，CALCULATE（）函數在應用中很少只使用一個參數，因為這并不能施展它的計算能力，因為如果CALCULATE（）函數只使用匯總參數，那么與直接將匯總參數拖曳至Power Pivot超級數據透視表值區域中的計算結果相同。本書接下來會頻繁地使用帶有兩個參數的CALCULATE（）函數，因為CALCULATE（）函數的篩選器參數能夠修改CALCULATE（）函數所處的數據透視表篩選環境，從而使CALCULATE（）函數能夠在新的數據透視表篩選環境中實現各種靈活的匯總計算。