上下文可讓您執行動態分析,其中公式的結果可以變更,以反映目前列或儲存格選取範圍,以及任何相關的資料。 對於建置高效能的公式、動態分析,以及針對公式中的問題進行疑難排解來說,有效地了解上下文和使用上下文非常重要。
本節定義了不同類型的上下文:列上下文、查詢上下文和篩選上下文。 它說明如何針對計算結果欄和樞紐分析表中的公式評估上下文。
這篇文章的最後一部分提供了詳細範例的連結,這些範例說明公式的結果如何隨著上下文變更。
了解上下文
Power Pivot 中的公式可能會受到樞紐分析表中套用的篩選、表格之間的關聯性,以及公式中所使用之篩選的影響。 上下文是可執行動態分析的原因。 對於建置公式和針對公式進行疑難排解來說,了解上下文非常重要。
有不同類型的上下文:列上下文、查詢上下文和篩選上下文。
列上下文可視為「目前列」。 如果您已建立計算結果欄,則列上下文是由每個列中的值,以及與目前列相關之欄中的值所組成。 還有一些函數 (EARLIER (英文) 與 EARLIEST (英文)) 從目前列取得值,然後在整個表格上執行作業時使用該值。
查詢上下文是指為樞紐分析表中的每個儲存格隱含建立的資料子集,取決於列與欄標題。
篩選上下文是每個欄中允許的一組值,取決於套用於列或是由公式中之篩選條件運算式定義的篩選條件限制式。
列上下文
如果您在計算結果欄中建立公式,則該公式的列上下文會在目前列中包含所有欄的值。 如果該表格與另一個表格相關,則該上下文還會包括另一個表格中與目前列相關的所有值。
例如,假設您建立了一個計算結果列 =[Freight] + [Tax],該數據行會將同一個數據表中的兩個數據行相加。 此公式的行為類似於 Excel 表格中的公式,該公式會自動參考同一列的值。 請注意,表格與範圍不同:您不能透過使用範圍標記法從目前列之前的列參考值,並且不能在表格或儲存格中參考任何任意的單一值。 您必須一律使用表格和欄。
列上下文會自動追蹤表格之間的關聯性,以判斷相關表格中的哪些列與目前列相關聯。
例如,下列公式會使用 RELATED 函數從相關的表格中擷取稅值,取決於訂單的出貨目的地區域。 透過使用目前表格中的區域值、查詢相關表格中的區域,然後從相關表格中取得該區域的稅率來確定稅值。
= [Freight] + RELATED('Region'[TaxRate])
此公式只是從區域表格中取得目前區域的稅率。 您不需要知道或指定連結表格的索引鍵。
多個列上下文
此外,DAX 包含了在表格上逐一計算的函數。 這些函數可以具有多個目前列和目前列上下文。 以程式設計來說,您可以建立遞迴內外循環的公式。
例如,假設您的活頁簿包含 Products 表格和 Sales 表格。 建議您瀏覽整個銷售表格,其中包含涉及多個產品的交易,並找出在任何一個交易中針對每個產品所訂購的最大數量。
在 Excel 中,此計算會需要一系列的中繼摘要,如果資料發生變更,則必須重新建置這些摘要。 如果您是 Excel 進階使用者,可能可以建置執行這項作業的陣列公式。 或者,您也可以在關聯式資料庫中撰寫巢狀的部分選取。
不過,您可以透過 DAX 建置一個傳回正確值的公式,而且隨時在您將資料新增至表格時自動更新結果。
=MAXX(FILTER(Sales,[ProdKey]=EARLIER([ProdKey])),Sales[OrderQty])
如需此公式的詳細逐步解說,請參閱 EARLIER 函數。
簡而言之,EARLIER 函數會儲存目前作業之前的作業的列上下文。 函數會隨時在記憶體中儲存兩組上下文:一組上下文代表公式內部迴圈的目前列,另一組上下文則代表公式外部迴圈的目前列。 DAX 會自動在兩個迴圈之間摘要值,以便您可以建立複雜的彙總。
查詢上下文
查詢上下文是指為公式隱含擷取的資料子集。 將量值或其他值的欄位拖放到樞紐分析表中的儲存格時,Power Pivot 引擎會檢查列和欄標題、交叉分析篩選器及報告篩選,以決定上下文。 然後,Power Pivot 進行必要的計算以填入樞紐分析表中的每個儲存格。 擷取的資料集是每個儲存格的查詢上下文。
由於上下文可以根據您放置公式的位置而變更,因此公式的結果也會根據您是在樞紐分析表中使用公式 (具有多個群組和篩選),還是在計算結果欄中使用公式 (沒有篩選與最少的上下文) 而變更。
例如,假設您建立這個簡單的公式,加總 Sales 數據表 [Profit] 欄中的值:
=SUM ('Sales'[Profit])
如果在 Sales 表格中的計算結果欄中使用此公式,則整份表格的公式結果將相同,因為公式的查詢上下文始終是 Sales 表格的完整資料集。 您的結果將是所有地區、所有產品、所有年份等的利潤。
不過,通常您不希望看到數百次相同的結果,而是希望取得特定年份、特定國家/地區、特定產品或這些產品某些組合的利潤,並取得總計。
在樞紐分析表中,透過新增或移除欄標題和列標題以及新增或移除交叉分析篩選器,就能輕鬆變更上下文。 您能以量值建立一個類似上面的公式,然後將其拖放到樞紐分析表。 每當在樞紐分析表新增欄標題或列標題時,都會變更評估量值的查詢上下文。 配量和篩選作業也會影響上下文。 因此,在樞紐分析表中使用的相同公式,會在每個儲存格的不同查詢上下文中進行評估。
篩選上下文
透過使用公式的引數,在欄或表格中允許的一組值上指定篩選條件限制式時,將新增篩選上下文。 篩選上下文會套用於其他上下文之上,例如列上下文或查詢上下文。
例如,樞紐分析表會根據列標題和欄標題計算每個儲存格的值,如前一節中有關查詢上下文的描述。 不過,在新增到樞紐分析表的量值或計算結果欄中,您可以指定篩選條件運算式來控制公式所使用的值。 您也可以選擇性地清除特定欄上的篩選。
如需如何在公式中建立篩選的詳細資訊,請參閱 Filter 函數 (英文)。
如需如何清除篩選以建立總計的範例,請參閱 ALL函數。
如需如何在公式中選擇性地清除並套用篩選的範例,請參閱 ALLEXCEPT 函數 (英文)。
因此,您必須檢閱樞紐分析表中使用之量值或公式的定義,以便在解譯公式的結果時了解篩選上下文。
決定在公式中的上下文
建立公式時,Power Pivot for Excel 首先會檢查一般語法,然後檢查您為目前上下文中可能的欄和表格所提供之欄和表格的名稱。 如果 Power Pivot 找不到公式指定的欄和表格,則您會收到錯誤訊息。
透過使用活頁簿中的可用表格、表格之間的任何關聯,以及已套用的任何篩選來決定上下文,如前面的節中所述。
例如,如果您剛剛將一些資料匯入到新的表格中,並且未套用任何篩選條件,則表格中的整組欄都是目前上下文的一部分。 如果您有依關聯性連結的多個表格,而且您正在透過新增欄標題以及使用交叉分析篩選器進行篩選的樞紐分析表中工作,則上下文包括相關表格和資料上的任何篩選。
上下文是一個強大的概念,也可能難以對公式進行疑難排解。 我們建議您從簡單的公式與關聯性開始,以查看上下文的運作方式,然後開始在樞紐分析表中測試簡單的公式。 下一節還提供了一些範例,說明公式如何使用不同類型的上下文來以動態方式傳回結果。
公式中上下文的範例
-
RELATED 函數會展開目前列的上下文,以包含相關欄中的值。 這可讓您執行查詢。 本主題中的範例說明了篩選和列上下文的互動。
-
FILTER 函數讓您能指定要包含在目前上下文中的列。 本主題中的範例還說明如何在執行彙總的其他函數中內嵌篩選。
-
ALL 函數會在公式中設定上下文。 您可以使用它來覆寫做為查詢上下文的結果套用的篩選。
-
ALLEXCEPT 函數讓您能移除所有篩選,除了您指定的篩選之外。 這兩個主題都包含範例,可以引導您建置公式,並了解複雜的上下文。
-
EARLIER 和 EARLIEST 函數讓您能透過執行計算來循環檢查表格,同時參照內部循環中的值。 如果您熟悉遞迴概念以及內部與外部迴圈,您會非常感謝 EARLIER 和 EARLIEST 函數提供的強大功能。 如果您不熟悉這些概念,則應仔細遵循範例中的步驟查看內部和外部上下文在計算中的使用方式。
參考完整性
本節將討論一些進階概念,這些概念與依關聯性連接之 Power Pivot 表格中遺漏的值相關。 如果您有包含多個表格和複雜公式的活頁簿,並希望協助您了解結果,那麼本節可能對您有幫助。
如果您不熟悉關聯式資料概念,我們建議您先閱讀關聯性概觀 (機器翻譯) 這個簡介主題。
參考完整性與 Power Pivot 關聯性
Power Pivot 不需要在兩個表格之間強制執行參考完整性以定義有效的關聯性。 相反地,會在每個一對多關聯性的「一端」建立一個空白列,並用來處理相關表格中所有不相符的列。 它會有效地以 SQL 外部聯結方式來運作。
在樞紐分析表中,如果您依關聯性的一端來將資料分組,則關聯性多端任何不相符的資料將會組成群組,並包含在具有空白列標題的總計中。 空白標題大致相當於「未知的成員」。
了解「未知的成員」
如果您曾使用多維度資料庫系統 (例如 SQL Server Analysis Services),那麼您可能很熟悉「未知的成員」的概念。 如果您不熟悉該術語,則下列範例說明什麼是「未知的成員」,以及它影響計算的方式。
假設您正在建立一個計算,該計算將每個商店的月銷售額加總,但 Sales 表格中的欄缺少商店名稱的值。 有鑑於 Store 和 Sales 表格是透過商店名稱連結的,您預期在公式中會發生什麼事? 樞紐分析表應該如何分組或顯示與現有商店無關的銷售數字?
此問題在資料倉儲中很常見,其中事實資料的大型表格在邏輯上必須與維度表格相關,維度表格包含有關用於分類及計算事實的商店、地區和其他屬性的資訊。 若要解決這個問題,任何與現有實體無關的新事實都會暫時指派給未知的成員。 這就是為什麼不相關的事實將以群組方式出現在樞紐分析表中的空白標題下。
比較空白值與空白列的處理方式
空白值與空白列不同,空白列是新增來容納未知的成員。 空白值是一個特殊值,用於表示 null、空字串及其他遺漏的值。 如需空白值以及其他 DAX 資料類型的詳細資訊,請參閱資料模型中的資料類型 (機器翻譯)。