Power Pivot 中的 DAX 案例

本節提供範例連結，示範如何在下列案例中使用 DAX 公式。

• 執行複雜的計算
• 使用文字和日期
• 條件值和錯誤測試
• 使用時間智慧
• 排名和比較值

本文內容

• 開始使用

• 案例：執行複雜的計算

  • 建立樞紐分析表的自訂計算
  • 將篩選套用至公式
  • 選擇性地移除篩選以建立動態比例
  • 使用外部迴圈中的值

• 案例：使用文字和日期

  • 串連建立索引欄
  • 根據從文字日期擷取的日期部分 Compose a date
  • 定義自訂日期或數字格式
  • 使用公式變更資料類型

• 案例：條件值和錯誤測試

  • 根據條件建立值
  • 測試公式中的錯誤

• 案例：使用時間智慧

  • 計算累計銷售額
  • 比較一段時間內的值
  • 計算超過自訂日期範圍的值

• 案例：排名和比較值

  • 只顯示樞紐分析表中的前十個項目
  • 使用公式動態訂購項目

開始使用

請瀏覽 DAX 資源中心 Wiki ，您可以在這裡找到各種有關 DAX 的資訊，包括由業界領先的專業人員和 Microsoft 提供的部落格、範例、白皮書和影片。

案例：執行複雜的計算

DAX 公式可以執行複雜的計算，其中涉及自訂彙總、篩選和條件值的使用。 本節提供如何開始使用自訂計算的範例。

建立樞紐分析表的自訂計算

CALCULATE 和 CALCULATETABLE 是功能強大、彈性的函數，可用於定義導出欄位。 這些函數可讓您變更執行計算的內容。 您也可以自訂要執行的彙總類型或數學運算。 如需範例，請參閱下列主題。

• 計算
• 可計算

將篩選套用至公式

在 DAX 函數採用資料表作為引數的大部分地方，您通常可以改為傳入篩選後的資料表，方法是使用 FILTER 函數代替資料表名稱，或將篩選運算式指定為其中一個函數引數。 下列主題提供如何建立篩選，以及篩選如何影響公式結果的範例。 如需詳細資訊，請參閱 篩選 DAX 公式中的資料。

FILTER 函數可讓您使用運算式來指定篩選準則，而其他函數則是專為篩選出空白值所設計。

• FILTER
• ALLNOBLANKROW
• FIRSTNONBLANK
• LASTNONBLANK

選擇性地移除篩選以建立動態比例

藉由在公式中建立動態篩選，您可以輕鬆回答下列問題：

• 目前產品的銷售額佔當年總銷售額的貢獻是多少？
• 與其他部門相比，該部門對所有營業年度的總利潤貢獻了多少？

您在樞紐分析表中使用的公式可能會受到樞紐分析表內容的影響，但您可以新增或移除篩選，以選擇性地變更內容。 ALL 主題中的範例說明如何執行此動作。 若要尋找特定轉銷商的銷售額與所有轉銷商的銷售量之比，您可以建立一個量值，計算目前內容的值除以 ALL 內容的值。

ALLEXCEPT 主題提供如何選擇性地清除公式篩選的範例。 這兩個範例都會逐步引導您了解結果如何根據樞紐分析表的設計而有所變更。

• 全部
• ALLEXCEPT

如需有關如何計算比率和百分比的其他範例，請參閱下列主題：

• ISBLANK
• ISERROR

使用外部迴圈中的值

除了在計算中使用目前內容中的值之外，DAX 還可以使用上一個迴圈中的值來建立一組相關的計算。 下列主題將提供如何建置參照外部迴圈之值的公式的逐步解說。 EARLIER 函數最多支援兩層巢狀迴圈。

• EARLIER 函數

若要深入瞭解資料列內容和相關資料表，以及如何在公式中使用這個概念，請參閱 DAX 公式中的內容。

案例：使用文字和日期

本節提供 DAX 參考主題的連結，其中包含涉及使用文字、擷取和撰寫日期和時間值，或根據條件建立值的常見案例範例。

串連建立索引欄

Power Pivot 不允許複合索引鍵;因此，如果您的資料來源中有複合索引鍵，您可能需要將它們合併成單一索引鍵資料行。 下列主題提供一個範例，說明如何根據複合索引鍵建立計算結果欄。

• CONCATENATE 函數

根據從文字日期擷取的日期部分 Compose a date

Power Pivot 使用 SQL Server 日期/時間資料類型來處理日期;因此，如果您的外部資料包含格式不同的日期，例如，您的日期是以 Power Pivot 資料引擎無法辨識的地區日期格式撰寫，或是您的資料使用整數代理索引鍵，您可能需要使用 DAX 公式來擷取日期部分，然後將各個部分組成有效的日期/時間表示法。

例如，假設您有一行日期是以整數表示，然後匯入為文字字串，您可以使用下列公式將字串轉換成日期/時間值：

=DATE (RIGHT ([Value1]，4) ，LEFT ([Value1]，2) ，MID ([Value1]，2) )

Value1	結果
01032009	1/3/2009
12132008	12/13/2008
06252007	6/25/2007

下列主題提供用來擷取和撰寫日期之函數的詳細資訊。

• DATEVALUE 函數
• TIMEVALUE 函數
• DATE 函數

定義自訂日期或數字格式

如果您的資料包含的日期或數字並非以標準 Windows 文字格式來表示，您可以定義自訂格式，以確保正確處理值。 這些格式用於將值轉換為字串或從字串轉換。 下列主題也提供可用來處理日期和數字之預先定義格式的詳細清單。

• FORMAT 函數的預先定義數值格式
• FORMAT 函數的自訂數值格式
• FORMAT 函數預先定義的日期和時間格式
• FORMAT 函數的自訂日期和時間格式

使用公式變更資料類型

在 Power Pivot 中，輸出的資料類型由來源資料行決定，且您無法明確指定結果的資料類型，因為最佳資料類型是由 Power Pivot 決定。 不過，您可以使用 Power Pivot 所執行的隱含資料類型轉換來操作輸出資料類型。 

• 若要將日期或數字字串轉換成數值，請乘以 1.0。 例如，下列公式會計算目前日期減去 3 天，然後輸出對應的整數值。
  = (TODAY () -3) *1.0
• 若要將日期、數字或貨幣值轉換成字串，請將值與空白字串串連。 例如，下列公式會以字串形式傳回今天的日期。
  =“”& TODAY ()

下列函式也可以用來確保傳回特定的資料類型：

將實數轉換成整數

• ROUND 函數
• CEILING 函數
• FLOOR 函數
  將實數、整數或日期轉換成字串
• FIXED 函數
• FORMAT 函數
  將字串轉換成實數或日期
• VALUE 函數
• DATEVALUE 函數
• TIMEVALUE 函數

案例：條件值和錯誤測試

與 Excel 一樣，DAX 也有函數可讓您測試資料中的值，並根據條件傳回不同的值。 例如，您可以建立計算資料行，根據每年的銷售額將轉銷商標示為 「偏好」 或 「值 」。 測試值的函數也可用於檢查值的範圍或類型，以防止非預期的資料錯誤中斷計算。

根據條件建立值

您可以使用巢狀 IF 條件來測試值，並有條件地產生新值。 下列主題包含一些條件式處理和條件值的簡單範例：

• IF
• OR 函數
• CONCATENATE 函數

測試公式中的錯誤

與 Excel 不同的是，計算結果欄的一列不能有有效的值，而另一列有無效的值。 也就是說，如果 Power Pivot 欄的任何部分發生錯誤，則會以錯誤標幟整欄，因此您必須永遠修正導致無效值的公式錯誤。

例如，如果您建立一個除以零的公式，可能會得到無窮大的結果或誤差。 如果函數預期數值時遇到空白值，某些公式也會失敗。 當您開發資料模型時，最好讓錯誤顯示，以便按一下訊息並對問題進行疑難排解。 不過，當您發佈活頁簿時，您應該納入錯誤處理，以防止非預期的值造成計算失敗。

若要避免在計算結果欄中傳回錯誤，您可以使用邏輯和資訊函數的組合來測試錯誤，並一律傳回有效的值。 下列主題提供一些簡單的範例，說明如何在 DAX 中執行此動作：

• ISBLANK 函數
• IFERROR 函數

案例：使用時間智慧

DAX 時間智慧函數包含可協助您從資料中擷取日期或日期範圍的函數。 然後，您可以使用這些日期或日期範圍來計算類似期間的值。 時間智慧函數也包含可與標準日期間隔搭配使用的函數，讓您跨月、跨年或跨季的值進行比較。 您也可以建立一個公式來比較指定期間的第一天和最後一天的值。

如需所有時間智慧函數的清單，請參閱 DAX) (時間智慧函數 。 如需有關如何在 Power Pivot 分析中有效使用日期和時間的秘訣，請參閱 Power Pivot 中的日期。

計算累計銷售額

下列主題包含如何計算期末和期初餘額的範例。 這些範例可讓您建立跨不同間隔（例如天、月、季或年）的滾存餘額。

• CLOSINGBALANCEMONTH 函數、 CLOSINGBALANCEQUARTER 函數、 CLOSINGBALANCEYEAR 函數
• OPENINGBALANCEMONTH 函數、 OPENINGBALANCEQUARTER 函數、 OPENINGBALANCEYEAR 函數

比較一段時間內的值

下列主題包含如何比較不同時段的總和的範例。 DAX 支援的預設時段為月、季和年。

• PREVIOUSMONTH 函數， PREVIOUSQUARTER，PREVIOUSYEAR 函數
• TOTALMTD 函數、 TOTALQTD 函數、 TOTALYTD 函數
• PARALLELPERIOD 函數

計算超過自訂日期範圍的值

如需如何擷取自訂日期範圍的範例，請參閱下列主題，例如促銷活動開始後的前 15 天。

• DATESINPERIOD 函數
• DATESBETWEEN 函數
• DATEADD 函數
• FIRSTDATE 函數
• LASTDATE 函數

如果您使用時間智慧函數來擷取自訂日期集，則可以使用該日期集作為執行計算之函數的輸入，以建立跨時段的自訂彙總。 請參閱下列主題，以取得如何執行這項操作的範例：

• PARALLELPERIOD 函數

  注意

  如果您不需要指定自訂日期範圍，但使用月、季或年等標準會計單位，建議您使用專門用於此目的的時間智慧函數執行計算，例如 TOTALQTD、TOTALMTD、TOTALQTD 等。

案例：排名和比較值

若要只顯示欄或樞紐分析表中前 n 個項目數目，您有數個選項：

• 您可以使用 Excel 中的功能來建立熱門篩選。 您也可以在樞紐分析表中選取一些頂端或底部的值。 本節的第一部分說明如何篩選樞紐分析表中前 10 個項目。 如需詳細資訊，請參閱 Excel 文件。
• 您可以建立動態排名值的公式，然後依排名值進行篩選，或將排名值當做交叉分析篩選器使用。 本節的第二部分將說明如何建立此公式，然後在交叉分析篩選器中使用該排名。

每種方法都有優點和缺點。

• Excel 頂端篩選很好用，但篩選只是用於顯示。 如果樞紐分析表的基礎資料變更，您必須手動重新整理樞紐分析表才能看到變更。 如果您需要動態使用排名，您可以使用 DAX 建立公式，將值與欄內其他值進行比較。
• DAX 公式更強大;此外，通過將排名值添加到切片器，您只需單擊切片器即可更改顯示的頂部值的數量。 不過，計算的計算成本很高，而且此方法可能不適用於具有許多資料列的資料表。

只顯示樞紐分析表中的前十個項目

顯示樞紐分析表中頂端或底部的值 在樞紐分析表中，按一下 [列標籤] 標題中的向下箭頭。 選取 值篩選前>10 個。 在 [ 前 10 名篩選欄 <名稱> ] 對話方塊中，選擇要排名的欄和值數目，如下所示： 選取 [頂端 ] 以查看含有最高值的儲存格，或選取 [底部 ] 以查看含有最低值的儲存格。 輸入您要查看的頂端或底部值數目。 預設值為 10。 選取您想要顯示值的方式： NameDescriptionItems選取此選項可篩選樞紐分析表，以僅依值顯示最上層或最後端項目的清單。百分比選取此選項可篩選樞紐分析表，只顯示加起來達指定百分比的項目。Sum選取此選項可顯示頂端或底部項目值的總和。 選取包含您想要排名之值的資料行。 按一下 [確定]。

使用公式動態訂購項目

下列主題包含如何使用 DAX 建立儲存在計算資料行中的排名的範例。 由於 DAX 公式是動態計算，因此即使基礎資料已變更，您也始終可以確定排名正確無誤。 此外，由於公式是在計算欄中使用，因此您可以使用 [交叉分析篩選器] 中的排名，然後選取前 5 名、前 10 名，甚至是前 100 名的值。

• EARLIER 函數。