PowerPivot 中的資料分析運算式 (DAX)

PowerPivot 中的資料分析運算式 (DAX)

資料分析運算式(DAX)首先會有很小的 intimidating,但不要讓名稱欺騙您。 DAX 基本功能非常容易理解。 首先,DAX 第一項是不是程式設計語言。 DAX 是一種公式語言。 您可以使用 DAX 來定義計算結果欄量值(也稱為計算欄位)的自訂計算。 DAX 包括 Excel 公式中使用的一些函數,以及專門用來處理關聯式資料及執行動態匯總的其他函數。

瞭解 DAX 公式

DAX 公式與 Excel 公式非常類似。 若要建立一個,請輸入等號,後面接著函數名稱或運算式,以及任何必要的值或引數。 就像 Excel 一樣,DAX 提供各種函數,您可以用來處理字串、使用日期和時間執行計算,或建立條件值。

不過,DAX 公式會依下列重要方式而有所不同:

  • 如果您想要依列自訂計算,DAX 包含的函數可讓您使用目前的資料列值或相關值來執行依內容而變化的計算。

  • DAX 包含一種函數類型,該函數會傳回 table 作為其結果,而不是單一值。 這些函數可以用來提供其他函數的輸入。

  • DAX 中的時間智慧函數可允許使用日期範圍進行計算,以及比較跨並行期間的結果。

在何處使用 DAX 公式

您可以在olumns中,或在計算fields中,在 Power Pivot 建立公式。

計算結果欄

[計算結果欄] 是您新增至現有 Power Pivot 資料表的資料行。 您可以建立 DAX 公式來定義資料行的值,而不是在欄中貼上或匯入值。 如果您在樞紐分析表(或樞紐分析圖)中包含 Power Pivot 資料表,計算結果欄可以用來做為任何其他資料行。

計算結果欄中的公式與您在 Excel 中建立的公式非常類似。 不過,在 Excel 中,您無法針對表格中不同的資料列建立不同的公式;相反地,DAX 公式會自動套用至整個資料行。

如果資料行包含公式,則會針對每個資料列計算值。 當您建立公式時,結果會立即針對該列進行計算。 只有基礎資料重新整理或使用手動重新計算時,才會重新計算欄值。

您可以建立以量值與其他計算結果欄為基礎的計算結果欄。 不過,請避免針對計算結果欄和量值使用相同的名稱,因為這可能會導致混亂的結果。 在參照欄時,最好是使用完全限定的欄參照,以避免意外地調用量值。

如需詳細資訊,請參閱Power Pivot 中的計算結果欄

機制

[量值] 是專為在使用 Power Pivot 資料的樞紐分析表(或樞紐分析圖)中建立的公式。 測度可以根據標準匯總函數(例如 COUNT 或 SUM)來建立,或者您可以使用 DAX 定義您自己的公式。 在樞紐分析表的 [] 區域中使用量值。 如果您想要將計算結果放在樞紐分析表的不同區域,請改為使用計算結果欄。

當您定義明確測量的公式時,除非您將度量值新增到樞紐分析表中,否則不會發生任何情況。 當您新增量值時,會針對樞紐分析表的 [] 區域中的每個儲存格評估公式。 因為會針對列和欄標題的每個組合建立結果,所以對於每個儲存格而言,測度的結果可能會不同。

您建立的量值定義會與其來源資料表格儲存在一起。 它會出現在 [樞紐分析表欄位] 清單中,且可供活頁簿的所有使用者使用。

如需詳細資訊,請參閱Power Pivot 中的量值

使用資料編輯列建立公式

Power Pivot(例如 Excel)提供資料編輯列,讓您更輕鬆地建立及編輯公式,以及自動完成功能,以將輸入及語法錯誤降至最低。

輸入資料表的名稱   開始輸入資料表的名稱。 [公式自動完成] 提供下拉式清單,其中包含以這些字母開頭的有效名稱。

輸入欄的名稱   輸入方括弧,然後從目前資料表中的欄清單中選擇資料行。 對於來自另一個資料表的資料行,開始輸入資料表名稱的第一個字母,然後從 [自動完成] 下拉式清單中選擇該資料行。

如需詳細資訊和如何建立公式的逐步解說,請參閱在 Power Pivot 中建立計算的公式

使用 [自動完成] 的秘訣

您可以在包含嵌套函數的現有公式中間使用 [公式自動完成]。 在插入點前緊接著的文字會用於顯示下拉式清單中的值,插入點之後的所有文字都保持不變。

您為常數建立的定義的名稱不會顯示在 [自動完成] 下拉式清單中,但是您仍然可以輸入它們。

Power Pivot 不會新增函數的右括弧或自動匹配括弧。 您應該確定每個函數語法正確,或是您無法儲存或使用公式。 

在公式中使用多個函數

您可以嵌套函數,這表示您使用某個函數的結果做為另一個函數的引數。 在計算結果欄中,您最多可以嵌套64層級的函數。 不過,嵌套可能會讓您難以建立或疑難排解公式。

許多 DAX 函數都是設計來完全用作嵌套函數。 這些函數會傳回表格,而該表無法直接儲存為結果;它應該提供給 table 函數的輸入。 例如,函數 SUMX、AVERAGEX 和 MINX 都需要資料表做為第一個引數。

附註: 在測度中存在函數嵌套的一些限制,以確保各欄之間相依性所需的許多計算不會影響效能。

比較 DAX 函數與 Excel 函數

DAX 函式程式庫是以 Excel 函式程式庫為基礎,但文件庫有許多差異。 本節摘要說明 Excel 函數與 DAX 函數之間的差異及相似之處。

  • 許多 DAX 函數都有相同的名稱和與 Excel 函數相同的一般行為,但在某些情況下已修改,可能會傳回不同的資料類型。 一般來說,您無法在 Excel 公式中使用 DAX 函數,或在 Power Pivot 中使用 Excel 公式,而不需要進行一些修改。

  • DAX 函數絕對不會採用儲存格參照或範圍作為參照,而是使用 DAX 函數做為參照的欄或表格。

  • DAX 日期和時間函數會傳回 datetime 資料類型。 相反地,Excel 日期和時間函數會傳回代表日期作為序列值的整數。

  • 許多新的 DAX 函數都傳回值的資料表,或根據值的資料表做為輸入的結果。 相反地,Excel 沒有傳回資料表的函數,但有些函數可以與陣列搭配使用。 在 Power Pivot中,輕鬆參照完整表格和欄的功能是一項新功能。

  • DAX 提供的新查閱函數與 Excel 中的陣列與向量查閱函數類似。 不過,DAX 函數需要在資料表之間建立關聯。

  • 資料行中的資料應該始終是相同的資料類型。 如果資料不是相同類型,DAX 會將整個資料行變更為最能容納所有值的資料類型。

DAX 資料類型

您可以從許多不同的資料來源,將資料匯入到 Power Pivot 的資料模型,可能支援不同的資料類型。 當您匯入或載入資料,然後在 [計算] 或 [樞紐分析表] 中使用資料時,資料會轉換成其中一個 Power Pivot 資料類型。 如需資料類型的清單,請參閱資料模型中的資料類型

Table 資料類型是 DAX 中用來做為多個新函數輸入或輸出的新資料類型。 例如,FILTER 函數會採用表格做為輸入,並輸出另一個資料表,其中只包含符合篩選準則的列。 透過結合匯總函數的資料表函數,您可以對動態定義的資料集執行複雜的計算。 如需詳細資訊,請參閱Power Pivot 中的聚合

公式與關聯式模型

[ Power Pivot] 視窗是一個區域,您可以在其中處理多個資料表,並連接關聯式模型中的資料表。 在這個資料模型中,資料表是依關聯性相互連接,這可讓您建立與其他資料表中資料行的關聯,並建立更有趣的計算。 例如,您可以建立加總關聯資料表值的公式,然後將該值儲存在單一儲存格中。 或者,若要控制相關資料表中的資料列,您可以將篩選套用至資料表和資料行。 如需詳細資訊,請參閱資料模型中資料表之間的關聯性。

因為您可以使用關聯性來連結資料表,所以您的樞紐分析表也可以包含來自不同資料表的多個資料行中的資料。

不過,因為公式可以使用整個資料表和資料行,所以您必須以不同于 Excel 的方式來設計計算。

  • 一般來說,欄中的 DAX 公式永遠會套用到欄中的整個值集合(永遠不只會套用到幾列或多個儲存格)。

  • Power Pivot 中的資料表在每個資料列中必須總是有相同的欄數,而且資料行中的所有列都必須包含相同的資料類型。

  • 如果資料表是由關聯來連接,您就應該確定用來做為鍵的兩個數據行具有相符的值。 因為 Power Pivot 不會強制執行參照完整性,所以在鍵欄中可能會有不相符的值,但仍會建立關聯。 不過,空白或非相符值的出現可能會影響樞紐分析表的公式結果和外觀。 如需詳細資訊,請參閱Power Pivot 公式中的查閱

  • 當您使用關聯性連結資料表時,您會放大範圍,或使用coNtext評估公式。 例如,樞紐分析表中的公式可能會受到任何篩選或欄和列標題的影響。 您可以撰寫操作內容的公式,但內容也可能會讓您的結果變更您可能不會進行的預期變更方式。 如需詳細資訊,請參閱DAX 公式中的內容

更新公式的結果

資料 r efresh和重新計算是兩種不同但相關的作業,在設計包含複雜公式、大量資料或從外部資料源取得的資料模型時,您應該瞭解。

[重新整理資料] 是使用外部資料源的新資料更新活頁簿中的資料的程式。 您可以以您指定的間隔手動重新整理資料。 或者,如果您已將活頁簿發佈至 SharePoint 網站,您可以從外部來源排程自動重新整理。

重新計算是更新公式結果以反映公式本身所做的變更,並在基礎資料中反映這些變更的程式。 重新計算可能會以下列方式影響效能:

  • 針對計算結果欄,每當您變更公式時,都會針對整個資料行重新計算公式的結果。

  • 針對量值,公式的結果不會計算,直到量值放在樞紐分析表或樞紐分析圖的內容中為止。 當您變更任何影響資料篩選或手動重新整理樞紐分析表的列或欄標題時,也會重新計算公式。

疑難排解公式

撰寫公式時的錯誤

如果您在定義公式時收到錯誤,公式可能會包含句法錯誤語意錯誤計算錯誤

句法錯誤是最簡單的解決方法。 它們通常會涉及遺失的括弧或逗號。 如需個別函數語法的說明,請參閱 DAX 函數參考。

當語法正確時,會發生其他錯誤類型,但參照的值或資料行在公式的內容中不會有意義。 這類語義及計算錯誤可能是由下列任何一個問題所造成:

  • 公式參照的是不存在的資料行、資料表或函數。

  • 公式似乎正確,但是當資料引擎提取資料時會發現類型不相符時,會引發錯誤。

  • 公式會將不正確的參數數或類型傳給函數。

  • 公式所參照的另一欄有錯誤,因此其值無效。

  • 公式參照的是尚未處理的資料行,這表示它有中繼資料,但沒有實際資料可供計算使用。

在前四個案例中,DAX 會標記包含無效公式的整個資料行。 在最後一個情況中,DAX 會將欄設成灰色,表示該資料行處於未處理狀態。

排名或排序欄值時發生不正確或不尋常的結果

當排名或排序包含值 NaN (不是數位)的資料列時,您可能會收到錯誤或意外的結果。 例如,如果計算將0除以0,則會傳回 NaN 結果。

這是因為公式引擎會依比較數值來執行排序與排名;不過,NaN 無法與資料行中的其他數位進行比較。

若要確保正確的結果,您可以使用 IF 函數的條件陳述式來測試 NaN 值,並傳回數位0值。

與 Analysis Services 表格式模型和 DirectQuery 模式的相容性

一般來說,您在 Power Pivot 中建立的 DAX 公式會完全相容分析服務的表格式模型。 不過,如果您將 Power Pivot 模型遷移至 Analysis Services 實例,然後以 DirectQuery 模式部署模型,就有一些限制。

  • 如果您在 DirectQuery 模式中部署模型,某些 DAX 公式可能會傳回不同的結果。

  • 當您將模型部署到 DirectQuery 模式時,某些公式可能會導致驗證錯誤,因為公式包含對關聯式資料來源不支援的 DAX 函數。

如需詳細資訊,請參閱 SQL Server 2012 Booksonlin 中的 Analysis Services 表格式模型檔。

附註:  本頁面是經由自動翻譯而成,因此文中可能有文法錯誤或不準確之處。 讓這些內容對您有所幫助是我們的目的。 告訴我們這項資訊是否有幫助? 這裡是供您參考的英文文章

增進您的 Office 技巧
探索訓練
優先取得新功能
加入 Office 測試人員

這項資訊有幫助嗎?

感謝您的意見反應!

感謝您的意見反應! 我們將協助您與我們的其中一個 Office 支援專員連絡以深入了解您的意見。

×