在本章中，我們將繼續(xù)探索DAX語言的強(qiáng)大，并詳細(xì)解釋單個(gè)函數(shù)：CALCULATE。實(shí)際上，相同的注意事項(xiàng)對(duì)于CALCULATETABLE也是有效的，它計(jì)算并返回表而不是標(biāo)量值。為簡(jiǎn)單起見，我們將在示例中引用CALCULATE，但請(qǐng)記住CALCULATETABLE具有相同的行為。
用一整章的篇幅來描述一個(gè)函數(shù)似乎有些奇怪，但這是必要的，因?yàn)樗呢S富性和副作用。 CALCULATE是迄今為止DAX語言中最重要、最有用和最復(fù)雜的函數(shù)。實(shí)際上，函數(shù)本身很簡(jiǎn)單。它只執(zhí)行很少的任務(wù)，但是需要計(jì)算的場(chǎng)景的數(shù)量，以及可以用計(jì)算編寫的公式的復(fù)雜性，使得完整的章節(jié)是絕對(duì)必要的。
正如前一章所述，這是一個(gè)艱難的過程。我們強(qiáng)烈建議您閱讀一次，大致了解一下CALCULATE，然后繼續(xù)閱讀該書的剩余部分。然后，當(dāng)你對(duì)一個(gè)特定的公式感到困惑時(shí)，回到這一章，從頭再讀一遍。您每次閱讀時(shí)都可能會(huì)發(fā)現(xiàn)新信息。
本章的另一個(gè)重要方面是我們需要有點(diǎn)迂腐。因此，如果在某個(gè)時(shí)候，你發(fā)現(xiàn)某個(gè)部分看起來很無聊，而且它似乎只是在陳述顯而易見的東西，那么再仔細(xì)閱讀一遍，以確保你完全理解了它。
5.1 理解CALCULATE
在上一章中，您已經(jīng)了解到有兩種不同的上下文：行上下文和篩選上下文。您已經(jīng)了解到可以使用迭代器以編程方式創(chuàng)建行上下文，并且學(xué)習(xí)了允許忽略篩選上下文的ALL函數(shù)。重要的是要記住ALL忽略篩選上下文，它不會(huì)改變它。因此，在以下公式中：
[Sales Amount Margin] :=
SUMX (
ALL ( Sales ),
Sales[SalesAmount] * AVERAGE ( Sales[MarginPct] )
)
ALL忽略現(xiàn)有的篩選上下文并始終返回整個(gè)表，但它不會(huì)以任何方式更改公式的其他部分的計(jì)算。實(shí)際上，在最內(nèi)層表達(dá)式中，AVERAGE將計(jì)算篩選上下文中MarginPct列的平均值，DAX在該上下文中計(jì)算整個(gè)表達(dá)式。 DAX中有一個(gè)函數(shù)可以更改篩選上下文，它是CALCULATE。
讓我們通過查看一個(gè)很有用的場(chǎng)景來介紹CALCULATE。想象一下，您想要構(gòu)建如圖5-1所示的報(bào)告，其中包含類別，子類別和銷售額總和。
報(bào)告顯示了每行占總行的百分比。您可以使用Microsoft Excel數(shù)據(jù)透視表功能輕松地生成這樣的報(bào)告，但是我們感興趣的是計(jì)算百分比作為度量值，以便用戶可以隨時(shí)將其添加到數(shù)據(jù)透視表中。
以下是一個(gè)天真的解決方案：
SalesPct :=
DIVIDE (
SUM ( Sales[SalesAmount] ),
SUMX ( ALL ( Sales ), Sales[SalesAmount] )
)
分子是SalesAmount的和。分母忽略篩選上下文，并始終返回SalesAmount的總計(jì)，而不管任何篩選器。只要您不從切片器中選擇任何內(nèi)容，此公式就可以使用。例如，如果在切片器中選擇黑色，則值為錯(cuò)誤；總計(jì)的百分比是18.76％而不是100％，因?yàn)橛糜诎俜直扔?jì)算的分母是一個(gè)更高的數(shù)字，如圖5-2所示。
這里的問題很容易理解。通過使用ALL，我們忽略了篩選上下文。因此，分母始終是所有銷售的總計(jì)，而如果選擇顏色，則希望將篩選器保持為顏色，僅清除類別和子類別上的篩選器。 ALL和迭代器不是這里的正確選擇；你需要更強(qiáng)大的東西。換句話說，您需要CALCULATE。
4.1.1 理解篩選上下文
在繼續(xù)介紹CALCULATE之前，插入一個(gè)題外話，這很重要，以加深您對(duì)篩選上下文的理解。篩選上下文是一個(gè)復(fù)雜的概念，只有在第10章“高級(jí)計(jì)算上下文”的末尾，您才能最終理解它是如何工作的。在此之前，我們按照復(fù)雜性的順序?qū)Y選上下文的進(jìn)行了不同的描述，以便一步一步地得到最終的解釋。
在上一章中，我們給出了是啊選上下文的第一個(gè)定義：“應(yīng)用于模型的一組篩選器，它們更改了整個(gè)數(shù)據(jù)庫(kù)中可見的行?！奔词顾且粋€(gè)正確的定義，它仍然非常幼稚。為了進(jìn)入下一個(gè)級(jí)別，您需要記住VertiPaq（DAX工作的數(shù)據(jù)庫(kù)）是一個(gè)列式數(shù)據(jù)庫(kù)。您應(yīng)該停止考慮表格，而不是考慮列。
例如，您可以將Product視為常規(guī)表，如圖5-3所示。
但是，由于VertiPaq是一個(gè)列式數(shù)據(jù)庫(kù)，因此表的正確表示形式將是一組列，而不是單個(gè)實(shí)體。因此，如圖5-4所示可以更好的理解同一個(gè)表
當(dāng)然，內(nèi)容是相同的，但是現(xiàn)在更容易將不同的列看作存儲(chǔ)在內(nèi)存中的不同項(xiàng)。顯然，相同的表示適用于數(shù)據(jù)模型中的任何表。因此，您應(yīng)該在思維上將模型中的每個(gè)表劃分為單獨(dú)的列，最后將得到一組列，邏輯上劃分為表，但每列與其他列分開。
將Color放入切片器時(shí)，DAX會(huì)對(duì)該列應(yīng)用篩選器。請(qǐng)仔細(xì)閱讀：它不會(huì)對(duì)包含該列的表格應(yīng)用篩選器。它僅將篩選器應(yīng)用于列。然后，因?yàn)樵摿惺潜淼囊徊糠?，因此該表也將具有篩選器。盡管如此，篩選器一次只能處理一列（我們正在描述實(shí)際發(fā)生的事情的近似；我們將在本書的后面部分對(duì)篩選上下文進(jìn)行最終理解）。
當(dāng)您僅在切片器篩選中選擇紅色產(chǎn)品時(shí)，模型將具有此篩選器，如圖5-5所示。
您可以想象將單個(gè)列上的篩選器表示為列的值上的位圖，或者以一種更易于閱讀的方式表示為列的活動(dòng)值列表。
最后，我們可以更好地定義篩選上下文：篩選器上下文是一組表。每個(gè)表都包含一個(gè)列，并列出該列的所有值，引擎認(rèn)為這些值在當(dāng)前上下文中可見。所有這些篩選器，放在一個(gè)邏輯AND中，形成篩選上下文。
在數(shù)據(jù)透視表的單個(gè)單元格中，您有來自切片器和篩選的篩選器；從行和列。每個(gè)篩選器都對(duì)一組表列進(jìn)行操作。因此，在前面的示例中，篩選上下文包含三個(gè)單獨(dú)的篩選器：一個(gè)用于類別，一個(gè)用于子類別（兩者都在數(shù)據(jù)透視表中的行上），另一個(gè)用于顏色（來自切片器）。
所有這些題外話使我們更好地理解更改篩選上下文的含義。如果要更改篩選上下文，則需要為模型中的部分或全部篩選列提供新的值列表。 DAX將使用新的值列表替換該列上的篩選器（僅限該列），并以此方式生成新的篩選上下文。
要記住的兩個(gè)重要方面是：
- 篩選器是一列的一組活動(dòng)值。
- 篩選器始終僅適用于單個(gè)列。
請(qǐng)記住，這不是篩選上下文的正確定義。在成為DAX的大師之前，你必須學(xué)習(xí)許多其他方面的知識(shí)。然而，這個(gè)定義對(duì)于開始使用篩選上下文已經(jīng)非常有用了。
在結(jié)束了題外話之后，您現(xiàn)在對(duì)篩選上下文有了更好的理解，我們可以繼續(xù)介紹CALCULATE是如何修改它的。
4.1.2 介紹CALCULATE
CALCULATE（及其同伴CALCULATETABLE，您將在稍后學(xué)習(xí)）是唯一可以修改篩選上下文的函數(shù)。實(shí)際上，CALCULATE會(huì)創(chuàng)建一個(gè)新的篩選上下文，然后在該新上下文中計(jì)算表達(dá)式。因?yàn)樾律舷挛牡钠瘘c(diǎn)是現(xiàn)有上下文，我們可以說它修改了計(jì)算上下文。
讓我們開始檢查CALCULATE的語法：
[Measure] := CALCULATE ( Expression, Condition1, ...
ConditionN )
CALCULATE接受任意數(shù)量的參數(shù)，唯一的強(qiáng)制參數(shù)是第一個(gè)，即要計(jì)算的表達(dá)式。我們?cè)诘谝粋€(gè)參數(shù)篩選器參數(shù)后面調(diào)用條件。 CALCULATE執(zhí)行以下操作：
- 它獲取當(dāng)前篩選上下文并將其復(fù)制到新的篩選上下文中。
- 它計(jì)算每個(gè)篩選器參數(shù)，并為每個(gè)條件生成該特定列的有效值列表。
- 如果兩個(gè)或多個(gè)篩選器參數(shù)影響同一列，則使用AND運(yùn)算符將它們合并在一起（或者，在數(shù)學(xué)術(shù)語中，使用集合交集）。
- 它使用新條件替換模型中列上的現(xiàn)有篩選器。如果列已有過濾器，則新過濾器將替換現(xiàn)有過濾器。另一方面，如果列沒有篩選器，則 DAX只是將新篩選器應(yīng)用于列。
- 一旦計(jì)算了新的篩選上下文，CALCULATE將計(jì)算新篩選上下文中的第一個(gè)參數(shù)（表達(dá)式）。最后，它將恢復(fù)原始篩選上下文，返回計(jì)算機(jī)結(jié)果。
備注：
CALCULATE執(zhí)行另一項(xiàng)非常重要的任務(wù)：它將任何現(xiàn)有行上下文轉(zhuǎn)換為等效的篩選上下文。本章稍后將對(duì)此主題進(jìn)行更詳細(xì)的討論。我們?cè)谶@里提到它的原因是，如果你對(duì)本節(jié)進(jìn)行第二次閱讀，最好記住這個(gè)非常重要的事實(shí)：CALCULATE從現(xiàn)有的行上下文中創(chuàng)建一個(gè)篩選上下文。
CALCULATE接受的篩選器可以有兩種類型：
- 值表，以表表達(dá)式的形式。在這種情況下，您需要提供要在新篩選上下文中查看的確切值列表。篩選器可以是具有單列或多列的表，就像整個(gè)表上的篩選器一樣。
- 布爾條件，例如Product [Color] =“White”。這些篩選器需要在單個(gè)列上工作，因?yàn)榻Y(jié)果必須是單個(gè)列的值列表。
如果使用帶有布爾條件的語法，DAX會(huì)將其轉(zhuǎn)換為值列表。所以每當(dāng)你寫：
[Sales Amount Red Products] :=
CALCULATE (
SUM ( Sales[SalesAmount] ),
Product[Color] = "Red"
)
DAX將表達(dá)式轉(zhuǎn)換為以下表達(dá)式：
[Sales Amount Red Products] :=
CALCULATE (
SUM ( Sales[SalesAmount] ),
FILTER (
ALL ( Product[Color] ),
Product[Color] = "Red"
))
因此，您只能使用布爾條件引用篩選器參數(shù)中的一列。 DAX必須檢測(cè)要在FILTER表達(dá)式中迭代的列，F(xiàn)ILTER表達(dá)式在后臺(tái)自動(dòng)生成。如果布爾表達(dá)式引用了更多列，那么您必須以顯式方式編寫FILTER迭代，稍后您將看到。
在這一點(diǎn)上，我們可以回到計(jì)算所有類別和子類別的銷售額占總銷售額的百分比的示例，仍然要考慮顏色上的過濾器。您可以使用CALCULATE編寫該度量值：
'''
[SalesPctWithCalculate] :=
DIVIDE (
SUM ( Sales[SalesAmount] ),
CALCULATE (
SUM ( Sales[SalesAmount] ),
ALL ( 'Product Category' ),
ALL ( 'Product Subcategory' )
))
'''
讓我們關(guān)注在這個(gè)公式的分母中使用的CALCULATE。要計(jì)算的表達(dá)式始終相同：銷售額的總和。但是，我們已經(jīng)知道公式本身可以有許多不同的值，這取決于DAX計(jì)算它的上下文。因?yàn)楸磉_(dá)式在CALCULATE內(nèi)，所以表達(dá)式的上下文不是原來的上下文。我們只需要理解上下文是如何進(jìn)行的，這些信息來自于CALCULATE的附加參數(shù)。
第一個(gè)篩選參數(shù)是ALL（'產(chǎn)品類別'）。 ALL返回表中的所有行，在本例中為所有類。 DAX檢索ProductCategory表并將其值用作新篩選器，替換產(chǎn)品類別的任何列上的任何先前存在的篩選器，在此示例中，該列來自數(shù)據(jù)透視表行。
顯然，對(duì)于它的第二個(gè)篩選器參數(shù)也是如此：ALL（'Product Subcategory'）從Product Subcategory的任何列中刪除任何篩選器。
重點(diǎn)是CALCULATE不會(huì)替換來自切片器的篩選器，即Color上的篩選器。它僅從Product Category和Product Subcategory表的列中刪除篩選器。因此，最終篩選上下文包含所有類別和子類別，但僅包含所選顏色。
在數(shù)據(jù)透視表中使用這個(gè)新公式可以顯示正確的值，如圖5-6所示。
這時(shí)，非常細(xì)心的讀者可能會(huì)停下來問:嗯，這沒有意義。您已從Product Category和Product Subcategory中刪除了篩選器，但是您要從Sales中對(duì)值進(jìn)行求和。誰從該表中刪除了篩選器？這確實(shí)是一個(gè)非常好的問題。事實(shí)上，我們的描述遺漏了一些非常重要的內(nèi)容：一旦CALCULATE計(jì)算出新的篩選上下文，它就會(huì)在計(jì)算表達(dá)式之前將其應(yīng)用于數(shù)據(jù)模型。
當(dāng)DAX將篩選上下文應(yīng)用于表時(shí)，我們已經(jīng)從上一章中了解到篩選器通過遵循其定義(單向或雙向篩選)的關(guān)系出傳遞。事實(shí)證明，我們從Product Category和Product Subcategory中刪除了篩選器，并且當(dāng)DAX應(yīng)用新的篩選上下文時(shí)，它將它傳遞到事實(shí)表，該事實(shí)表位于從Product Category開始的關(guān)系鏈的多端，以Sales結(jié)束。通過刪除Product Category和Product Subcategory中的篩選器，我們還刪除了它們?cè)赟ales中相應(yīng)的傳遞篩選器。
5.2 CALCULATE示例
現(xiàn)在您已經(jīng)了解了CALCULATE的基礎(chǔ)知識(shí)，或者至少您已經(jīng)了解了它為何如此有用，本章接下來的部分將專門介紹它的各種用法示例。它們對(duì)于深入學(xué)習(xí)和理解很重要。實(shí)際上，CALCULATE本身就是一個(gè)非常簡(jiǎn)單的函數(shù)。復(fù)雜性（因此我們給它的重要性）來自這樣一個(gè)事實(shí)，即使用CALCULATE，你必須根據(jù)篩選上下文進(jìn)行思考，并并且您可能會(huì)在一個(gè)公式中得到多個(gè)上下文，這使得代碼流程難以理解。根據(jù)我們的經(jīng)驗(yàn)（作為培訓(xùn)師），通過示例學(xué)習(xí)是理解CALCULATE和篩選上下文的最佳方式。
5.2.1 篩選單個(gè)列
使用CALCULATE最簡(jiǎn)單的方法是篩選單個(gè)列。例如，假設(shè)您要?jiǎng)?chuàng)建一個(gè)始終返回黑色產(chǎn)品銷售額的度量值，無論對(duì)顏色進(jìn)行何種選擇。該公式非常容易得出：
[SalesAmountBlack] :=
CALCULATE (
SUM ( Sales[SalesAmount] ),
Product[Color] = "Black"
)
如果在數(shù)據(jù)透視表中使用上一個(gè)公式，您將得到如圖5-7所示的結(jié)果。
您可以看到SalesAmountBlack始終顯示黑色產(chǎn)品的銷售額，即使在行上篩選上下文選擇不同顏色也是如此。
如果您關(guān)注第三行（藍(lán)色），則會(huì)發(fā)生以下情況：公式在篩選上下文中開始計(jì)算，其中顏色的唯一值為藍(lán)色。然后，CALCULATE計(jì)算了一個(gè)新條件（Color = Black），當(dāng)將其應(yīng)用于新的篩選器上下文時(shí)，它替換了現(xiàn)有條件，刪除了Blue上的篩選器并將其替換為Black上的篩選器。這發(fā)生在所有行（即所有顏色）上，這就是為什么您看到所有行的相同數(shù)字的原因。
顯然，因?yàn)镃ALCULATE覆蓋選擇的唯一列是顏色，其他列保持其篩選器。例如，如果將日歷年放在列上，您會(huì)看到所有顏色的結(jié)果始終相同，但不同年份的結(jié)果會(huì)發(fā)生變化，如圖5-8所示。
過濾單個(gè)列很簡(jiǎn)單。一個(gè)不太明顯的事實(shí)是，如果您使用條件作為CALCULATE的篩選器，則一次只能篩選一列。例如，您可能希望創(chuàng)建一個(gè)度量值，僅計(jì)算單位價(jià)格至少是單位成本兩倍的產(chǎn)品的銷售額。你可以試試這個(gè)公式：
[HighProfitabilitySales]：= CALCULATE（SUM（Sales [SalesAmount]），Product [Unit Price]> = Product [Unit Cost] * 2）
您可以看到，這次，條件涉及兩列：Unit Cost和Unit Price。即使DAX可以輕松計(jì)算每個(gè)產(chǎn)品的條件，它也不允許這種語法。原因在于，在其計(jì)算算法中，CALCULATE無法確定條件是否應(yīng)替換Unit Cost、Unit Price或其中任何一個(gè)上的任何現(xiàn)有過濾器。事實(shí)上，如果你試著寫上面的公式，你會(huì)得到一個(gè)錯(cuò)誤的結(jié)果：
Calculation error in measure 'Sales'[HighProfitabilitySales]:
The expression contains
multiple columns, but only a single column can be used in a
True/False expression that is
used as a table filter expression.
沒有辦法使用布爾語法編寫這樣的公式。如果需要在條件中使用多個(gè)列調(diào)用CALCULATE，則需要使用不同的語法，它提供值列表而不是條件。
編寫上一個(gè)表達(dá)式的正確方法是使用以下語法：
[HighProfitabilitySales] :=
CALCULATE (
SUM ( Sales[SalesAmount] ),
FILTER ( Product, Product[Unit Price] >= Product[Unit Cost] *
2 )
)
這次我們沒有使用布爾表達(dá)式，而是使用Table語法作為CALCULATE的filter參數(shù)。而且，我們不只篩選了一列；我們篩選了整個(gè)Product表。在圖5-9中，您可以看到HighProfitabilitySales度量值的實(shí)際應(yīng)用。
在本例中，CALCULATE計(jì)算條件：FILTER的結(jié)果是一個(gè)包含多個(gè)列的表(它包含Product的所有列)。當(dāng)新條件被插入到篩選上下文中時(shí)，實(shí)際上Product上的所有現(xiàn)有條件都將替換為此新篩選器。換句話說，使用實(shí)際表作為FILTER函數(shù)的第一個(gè)參數(shù)，可以有效地替換該表所有列上的所有條件。
閱讀了前面的解釋后，您應(yīng)該注意到這里有些事情不是很清楚。我們說CALCULATE中的FILTER表達(dá)式替換了Product表中所有先前存在的篩選器，因?yàn)镕ILTER返回的表包含Product的所有列。然而，我們的公式返回的值對(duì)于每一行是不同的。
例如，在藍(lán)色行上，HighProfitabilitySales返回具有高盈利能力的藍(lán)色產(chǎn)品的銷售額，即使，就我們目前所知，它應(yīng)該返回所有高利潤(rùn)產(chǎn)品的銷售額，而不考慮顏色。要么 DAX沒有替換顏色上的篩選器，要么發(fā)生了更復(fù)雜的事情。因?yàn)槲覀円呀?jīng)知道DAX替換了顏色上的篩選器，所以我們需要進(jìn)行更多的研究來理解正確的計(jì)算流程。下面的代碼是度量值的公式:：我們對(duì)行進(jìn)行了編號(hào)，以便更容易引用公式的各個(gè)部分。
1. CALCULATE (
2. SUM ( Sales[SalesAmount] ),
3. FILTER (
4. Product,
5. Product[Unit Price] >= Product[Unit Cost] * 2
6. )
7. )
第一個(gè)函數(shù)是CALCULATE。我們知道CALCULATE作為其第一步，計(jì)算篩選器參數(shù)。在執(zhí)行任何其他操作之前，CALCULATE將從第3行開始計(jì)算FILTER表達(dá)式。
FILTER是一個(gè)迭代器，它遍歷Product表（參見第4行）。FILTER不會(huì)看到所有產(chǎn)品；它只能看到當(dāng)前篩選上下文中可見的行?，F(xiàn)在，問題是DAX在哪個(gè)篩選上下文中計(jì)算第4行上的Product ？請(qǐng)記住，CALCULATE仍然沒有創(chuàng)建新的篩選上下文。稍后，在評(píng)估篩選器之后，它將執(zhí)行此操作。您可以推斷出CALCULATE中的篩選是在原始篩選上下文下評(píng)估的，而不是在CALCULATE創(chuàng)建的篩選上下文下。雖然這似乎是顯而易見的，但在許多DAX公式中，這種簡(jiǎn)單的考慮是錯(cuò)誤的主要來源之一。
第4行的Product是指在原始篩選上下文中可見的產(chǎn)品。對(duì)于圖5-9中的藍(lán)色行，該上下文僅顯示藍(lán)色產(chǎn)品。因此，F(xiàn)ILTER將迭代藍(lán)色產(chǎn)品，它將只返回具有高盈利能力的藍(lán)色產(chǎn)品。然后CALCULATE將刪除現(xiàn)有的顏色篩選器，但這樣的篩選器現(xiàn)在已經(jīng)包含在FILTER的結(jié)果中，從而導(dǎo)致您正在觀察的行為。重要的是要正確理解篩選的流程，在CALCULATE計(jì)算的表達(dá)式內(nèi)替換顏色上的篩選器，而不是在CALCULATE篩選器內(nèi)部。換句話說，CALCULATE的篩選器參數(shù)在先前的篩選上下文中進(jìn)行評(píng)估。只有稍后的CALCULATE才會(huì)創(chuàng)建新的篩選上下文，在該上下文中計(jì)算其表達(dá)式參數(shù)。
要獲得完整的圖片，您可以查看以下公式：
[HighProfitabilityALLSales] :=
CALCULATE (
SUM ( Sales[SalesAmount] ),
FILTER (
ALL ( Product ),
Product[Unit Price] >= Product[Unit Cost] * 2
))
這一次，我們使用FILTER（ALL（Product））而不是使用FILTER（Product）。 FILTER不會(huì)只迭代藍(lán)色產(chǎn)品；它將始終迭代所有產(chǎn)品，并且因?yàn)镃ALCULATE替換顏色上的篩選器，行為如圖5-10所示。
HighProfitabilityALLSales始終顯示所有高盈利產(chǎn)品的銷售額，有效地忽略了先前存在的顏色篩選器。
讓我們從第一個(gè)例子開始得出一些結(jié)論。
- 您可以在CALCULATE中使用布爾條件，但是為了使用它們，您只能在表達(dá)式中引用一列，否則您將收到語法錯(cuò)誤。
- 您可以在CALCULATE中使用FILTER或任何其他表函數(shù)作為篩選器參數(shù)，在這種情況下，表中的所有列都是新篩選器上下文的一部分。這意味著CALCULATE將替換這些列上的任何現(xiàn)有篩選器。
- 如果使用FILTER，則CALCULATE將在原始篩選上下文中評(píng)估FILTER。另一方面，如果使用布爾條件，則CALCULATE將替換現(xiàn)有的篩選上下文，但僅替換受影響的列。
5.2.2 用復(fù)雜條件進(jìn)行篩選
當(dāng)您使用多個(gè)篩選器時(shí)，CALCULATE在創(chuàng)建新篩選上下文時(shí)會(huì)對(duì)其所有篩選條件執(zhí)行邏輯AND。因此，如果您想篩選Tailspin Toys生產(chǎn)的所有黑色產(chǎn)品，您可以使用如下表達(dá)式：
[Calculate Version] :=
CALCULATE (
SUM ( Sales[SalesAmount] ),
Product[Brand] = "Tailspin Toys",
Product[Color] = "Black"
)
因?yàn)镃ALCULATE將這兩個(gè)條件放在AND中，您可能會(huì)認(rèn)為表達(dá)式的這個(gè)公式是等價(jià)的：
[FILTER Version] :=
CALCULATE (
SUM ( Sales[SalesAmount]),
FILTER (
Product,
AND (
Product[Brand] = "Tailspin Toys",
Product[Color] = "Black"
)))
實(shí)際上，這兩個(gè)表達(dá)式是不同的，本節(jié)解釋了原因。您已經(jīng)了解了這一點(diǎn)，但由于本主題的復(fù)雜性以及本節(jié)后面討論的概念的重要性，值得重復(fù)一遍。在帶有布爾表達(dá)式的公式中，品牌和顏色都會(huì)忽略現(xiàn)有的篩選上下文；而在使用FILTER的公式中，兩列都考慮了之前存在的篩選上下文（在應(yīng)用公式之前）。
因此，Calculate Version始終返回黑色Tailspin Toys的銷售額，而FILTER Version只返回在之前存在的篩選上下文中已經(jīng)存在的銷售額；否則返回一個(gè)空值。您可以在圖5-11中觀察到此行為。
不同之處在于，F(xiàn)ILTER會(huì)迭代由外部篩選上下文篩選的表。請(qǐng)記住以下公式：
[Sales of Tailspin Toys] :=
CALCULATE (
SUM ( Sales[SalesAmount] ),
Product[Brand] = "Tailspin Toys",
)
相當(dāng)于下一個(gè)：
[Sales of Tailspin Toys] :=
CALCULATE (
SUM ( Sales[SalesAmount] ),
FILTER (
ALL ( Product[Brand] ),
Product[Brand] = "Tailspin Toys",
))
在第二個(gè)公式中，通過使用ALL（Product [Brand]），我們明確要求忽略制造商列的當(dāng)前篩選上下文。我們?cè)僭趺磸?qiáng)調(diào)理解這些公式的重要性也不為過。即使這里使用的表達(dá)式用于教育目的，在編寫自己的表達(dá)式時(shí)也會(huì)遇到類似的情況，并確保最終會(huì)看到奇怪的結(jié)果。為了理解公式的行為，您必須了解上下文行為。
在單個(gè)列上工作，上述等價(jià)可以正常工作。在我們的示例中，我們有兩列，您可能會(huì)嘗試通過以下公式將等效擴(kuò)展到多列場(chǎng)景：
FilterAll Version :=
CALCULATE (
SUM ( Sales[SalesAmount] ),
FILTER (
ALL ( Product ),
AND (
Product[Brand] = "Tailspin Toys",
Product[Color] = "Black"
)))
此公式不滿足您在本節(jié)中看到的第一個(gè)公式所解決的要求。如果使用ALL（Product），則通過忽略整個(gè)表上的篩選上下文來忽略兩列上的篩選上下文。但是，通過忽略整個(gè)表上的篩選上下文，您仍然會(huì)得到不同的行為。為了看到效，我們需要使數(shù)據(jù)透視表更復(fù)雜。在圖5-12中，我們?cè)谛猩咸砑恿祟悇e名稱。
如您所見，F(xiàn)ilterAll Version忽略整個(gè)Product表上的篩選上下文，即使對(duì)于Computers類別也顯示該值，Calculate Version顯示一個(gè)空值。原因是Calculate Version僅忽略顏色和品牌名稱的篩選上下文，而FilterAll Version忽略整個(gè)表上的篩選上下文，從而忽略該類別。
為了找到正確的公式，你必須用列而不是表來考慮。我們既不能將Product表提供給FILTER（因?yàn)樗暾脑己Y選上下文），也不能提供ALL（Product）（因?yàn)樗雎粤怂泻Y選器）。相反，我們需要計(jì)算一個(gè)Product表，我們?cè)谄渲袆h除了制造商上的篩選器，但其中任何其他現(xiàn)有篩選器仍處于活動(dòng)狀態(tài)。我們已經(jīng)知道了一個(gè)函數(shù)，它允許我們以這種非常精細(xì)的方式處理篩選上下文：它是CALCULATE。唯一的問題是CALCULATE需要一個(gè)返回單個(gè)值的表達(dá)式，這次我們想要返回一個(gè)整表，因?yàn)槲覀冃枰鳛镕ILTER函數(shù)的參數(shù)。幸運(yùn)的是，有一個(gè)CALCULATE的伴侶，而不是返回單個(gè)值，返回一個(gè)表。它是CALCULATETABLE，將在下一節(jié)介紹。
5.2.3 使用CALCULATETABLE
CALCULATETABLE的工作方式與CALCULATE相同。唯一的區(qū)別在于結(jié)果的類型：CALCULATE計(jì)算返回標(biāo)量值，而CALCULATETABLE計(jì)算表表達(dá)式并返回表。下一個(gè)公式完全按照我們的需要執(zhí)行：它從Brand和Color中刪除篩選上下文，但讓其他過濾器在FILTER函數(shù)內(nèi)部流動(dòng)。
[CalcTable Version] :=
CALCULATE (
SUM ( Sales[SalesAmount] ),
FILTER (
CALCULATETABLE (
Product,
ALL ( Product[Brand] ),
ALL ( Product[Color] )
),
AND (
Product[Brand] = "Tailspin Toys",
Product[Color] = "Black"
)))
如圖5-13所示，CalcTable Version中的最后一個(gè)公式計(jì)算正確的值，該值與Calculate Version返回的值相同。
這種關(guān)于等價(jià)結(jié)果的題外話很重要，因?yàn)榱私鈱⒉紶柡Y選器轉(zhuǎn)換為FILTER等效項(xiàng)的正確方法將極大地幫助您處理更復(fù)雜的條件。例如，如果您想表達(dá)OR條件而不是AND條件，則需要使用此方法。
例如，如果您想計(jì)算所有品牌和顏色的總和，其中條件是品牌可以是Tailspin Toys或顏色是黑色（OR條件），那么您需要使用CALCULATETABLE定義它，如下面的代碼：
[CalcTable Version OR] :=
CALCULATE (
SUM ( Sales[SalesAmount] ),
FILTER (
CALCULATETABLE (
Product,
ALL ( Product[Brand] ),
ALL ( Product[Color] )
),
OR (
Product[Brand] = "Tailspin Toys",
Product[Color] = "Black"
)))
實(shí)際上，使用CALCULATETABLE是一種從Brand和Color中刪除篩選器的便捷方法，而不影響其他篩選。因此，使用簡(jiǎn)單的CALCULATE可以輕松解決具有多列的AND條件，因?yàn)镃ALCULATE會(huì)自動(dòng)將其所有篩選器參數(shù)放入AND中。然而，不同列之間的OR條件要復(fù)雜得多，因?yàn)槟荒芤蕾嘋ALCOULATE的自動(dòng)AND，并且需要手動(dòng)編寫復(fù)雜的DAX代碼。
值得注意的是，作為替代公式，您還可以使用以下代碼，該代碼使用包含兩列的ALL函數(shù)：
[ALL Version OR] :=
CALCULATE (
SUM ( Sales[SalesAmount] ),
FILTER (
ALL ( Product[Brand], Product[Color] ),
OR (
Product[Brand] = "Tailspin Toys",
Product[Color] = "Black"
)))
后一種方法更加優(yōu)雅，即使在開始時(shí)它也不是很直觀。
在本節(jié)中，您已經(jīng)看到，只要您使用多個(gè)列，或者在一般情況下使條件更加復(fù)雜，結(jié)果就會(huì)變得難以理解。即使是經(jīng)驗(yàn)豐富的DAX程序員也經(jīng)常發(fā)現(xiàn)很難把握計(jì)算流程。因此，不要被這一節(jié)的復(fù)雜性所嚇倒；只有經(jīng)驗(yàn)才能引導(dǎo)您走向自然的道路，在那里您將學(xué)習(xí)如何乍看之下閱讀公式。
5.3 理解上下文轉(zhuǎn)換
我們之前曾預(yù)料到CALCULATE會(huì)執(zhí)行另一項(xiàng)非常重要的任務(wù)：它將任何現(xiàn)有的行上下文轉(zhuǎn)換為等效的篩選上下文。
為了演示該行為，您需要?jiǎng)?chuàng)建一個(gè)包含CALCULATE表達(dá)式的計(jì)算列。計(jì)算列始終具有行上下文，因此這將觸發(fā)上下文轉(zhuǎn)換。例如，您在Product中定義包含以下DAX表達(dá)式的計(jì)算列：
Product[SumOfUnitPrice] = SUM ( Product[Unit Price] )
此公式匯總了所有產(chǎn)品的所有清單價(jià)格。表達(dá)式在行上下文中進(jìn)行計(jì)算，沒有篩選上下文，因此它返回表中所有產(chǎn)品的單位總價(jià)格，而不是計(jì)算它的產(chǎn)品的定價(jià)。您可以在圖5-14中看到該行為。
現(xiàn)在，您可以使用稍微修改的表達(dá)式版本創(chuàng)建新的計(jì)算列，這次涉及CALCULATE：
Product[SumOfUnitPriceCalc] = CALCULATE ( SUM ( Product[Unit
Price] ) )
什么？使用單個(gè)表達(dá)式參數(shù)進(jìn)行CALCULATE？篩選器在哪里？無處。事實(shí)上，我們使用最簡(jiǎn)單的CALCULATE形式。我們之前說過，CALCULATE的唯一強(qiáng)制性參數(shù)是第一個(gè)，所以在沒有任何篩選器的情況下調(diào)用CALCULATE是完全可以的。在這種情況下，CALCULATE不會(huì)更改現(xiàn)有的篩選上下文與其他條件，但它仍然執(zhí)行您現(xiàn)在正在學(xué)習(xí)的行為：它采用現(xiàn)有的行上下文（如果有）并將它們轉(zhuǎn)換為等效的篩選上下文。請(qǐng)注意，所有現(xiàn)有的行上下文都會(huì)合并到新的篩選上下文中，我們稍后會(huì)詳細(xì)介紹。
在該示例中，CALCULATE搜索現(xiàn)有行上下文，并發(fā)現(xiàn)Product上有一個(gè)，因?yàn)镃ALCULATE在定義計(jì)算列中運(yùn)行。 CALCULATE接受此行上下文并將其替換為僅包含當(dāng)前由行上下文迭代的行的篩選上下文。
我們將此行為稱為上下文轉(zhuǎn)換。一般來說，我們說CALCULATE執(zhí)行上下文轉(zhuǎn)換，將所有行上下文合并為一個(gè)新的等效篩選上下文。
在CALCULATE內(nèi)部，表達(dá)式SUM (Product[Unit Price])在只包含當(dāng)前產(chǎn)品行的篩選上下文中計(jì)算其值，這是因?yàn)镃ALCULATE執(zhí)行了上下文轉(zhuǎn)換。這一次的結(jié)果是相同的產(chǎn)品單價(jià)，如圖5-15所示。
第一次觀察到這種行為時(shí)，很難理解為什么CALCULATE執(zhí)行上下文轉(zhuǎn)換。在您開始使用此特性后，您會(huì)愛上它，因?yàn)槟鷮⒛軌蚓帉憦?qiáng)大的公式。
此外，上下文轉(zhuǎn)換還有另一個(gè)非常重要的副作用。您可能還記得篩選上下文和行上下文在關(guān)系中的行為方式不同：行上下文不會(huì)自動(dòng)通過關(guān)系傳遞，而篩選上下文會(huì)隨關(guān)系傳遞。因此，當(dāng)發(fā)生上下文轉(zhuǎn)換時(shí)，篩選上下文會(huì)自動(dòng)傳遞到相關(guān)表。
如果您使用以下定義創(chuàng)建兩個(gè)仍在Product中的新計(jì)算列，而不是對(duì)列表價(jià)格求和，則可以觀察到此行為：
Product[SalesAmount] = SUM ( Sales[SalesAmount] )
Product[SalesAmountCalc] = CALCULATE ( SUM (
Sales[SalesAmount] ) )
在圖5-16中，您可以看到結(jié)果，這顯然是不同的。
如您所見，SalesAmount列包含所有銷售的總計(jì)，SalesAmountCalc包含當(dāng)前產(chǎn)品的銷售額。 CALCULATE的存在以及Product上行上下文的相關(guān)上下文轉(zhuǎn)換將篩選器傳播到Sales，僅顯示一個(gè)產(chǎn)品的銷售額。
請(qǐng)注意，執(zhí)行CALCULATE時(shí)，所有活動(dòng)的上下文都會(huì)發(fā)生上下文轉(zhuǎn)換。實(shí)際上，不同的表上可能有多個(gè)行上下文。例如，如果在Product中創(chuàng)建的計(jì)算列中使用AVERAGEX迭代客戶，則兩個(gè)行上下文（Product和Customer）都會(huì)發(fā)生上下文轉(zhuǎn)換，Sales表將接收這兩個(gè)篩選器。請(qǐng)考慮以下表達(dá)式：
Product[SalesWithSUMX] =
AVERAGEX (
Customer,
CALCULATE ( SUM ( Sales[SalesAmount] ) )
)
該公式計(jì)算客戶購(gòu)買此產(chǎn)品所花費(fèi)的平均金額（不是平均價(jià)格，而是花費(fèi)的總金額的平均值）。 CALCULATE中的SUM在篩選上下文中執(zhí)行，該篩選上下文僅顯示當(dāng)前客戶（由AVERAGEX迭代）和當(dāng)前產(chǎn)品（由計(jì)算列評(píng)估迭代）的銷售額。這里有一個(gè)簡(jiǎn)單的方法來記住這個(gè)規(guī)則：在CALCULATE內(nèi)部沒有行上下文；僅存在篩選上下文。
5.3.1 理解度量值中的上下文轉(zhuǎn)換
由于DAX的另一個(gè)隱藏方面，理解上下文轉(zhuǎn)換非常重要。到目前為止，我們總是使用函數(shù)和列在CALCULATE中編寫表達(dá)式。但是，您也可以編寫包含度量值調(diào)用的表達(dá)式。如果從計(jì)算列內(nèi)部調(diào)用度量值，會(huì)發(fā)生什么？更一般地說，如果從行上下文中調(diào)用度量值會(huì)發(fā)生什么？
例如，您可以通過以下方式定義度量值SumOfSalesAmount：
[SumOfSalesAmount] := SUM ( Sales[SalesAmount] )
然后，您可以使用以下更簡(jiǎn)單的代碼定義SalesWithSUMX計(jì)算列：
Product[SalesWithSUMX] =
SUMX (
Customer,
CALCULATE ( [SumOfSalesAmount] )
)
CALCULATE的存在表明發(fā)生了上下文轉(zhuǎn)換。問題在于，無論何時(shí)從另一個(gè)表達(dá)式中調(diào)用度量值，DAX都會(huì)自動(dòng)將度量值封裝在CALCULATE中。因此，前一個(gè)表達(dá)式的行為與以下表達(dá)式相同：
Product[SalesWithSUMX] =
SUMX (
Customer,
[SumOfSalesAmount]
)
這次，公式中沒有可見的CALCULATE，但由于DAX添加了自動(dòng)CALCULATE，因此發(fā)生了上下文轉(zhuǎn)換。
這就是為什么總是編寫區(qū)分列和度量值的代碼非常重要的原因。 DAX作者使用的實(shí)際標(biāo)準(zhǔn)是避免將表名放在度量值前面，并且總是在列的前面加上表名。實(shí)際上，在上一個(gè)公式中，SumOfSalesAmount之前缺少表名表明SumOfSalesAmount是一個(gè)度量值，因此，您知道上下文轉(zhuǎn)換發(fā)生了。
自動(dòng)上下文轉(zhuǎn)換使得編寫通過迭代執(zhí)行復(fù)雜計(jì)算的公式變得非常容易。話雖如此，您需要一些時(shí)間才能熟悉閱讀和使用它。例如，如果您想僅計(jì)算購(gòu)買超過總體平均水平的客戶的銷售總額，則可以按以下方式編寫度量值：
[SalesMoreThanAverage] :=
VAR
AverageSales = AVERAGEX ( Customer, [SumOfSalesAmount] )
RETURN
SUMX (
Customer,
IF (
[SumOfSalesAmount] > AverageSales,
[SumOfSalesAmount]
))
在前面的代碼中，我們使用SumOfSalesAmount作為不同行上下文中的度量值。在變量定義中，我們使用它來計(jì)算客戶銷售的平均值，而在使用SUMX的迭代中，我們使用它來檢查當(dāng)前客戶的銷售額與之前存儲(chǔ)在變量中的平均值。
警告
基于VAR的語法更易于閱讀和維護(hù)（也可能更快）。但是，無論您使用的DAX版本如何，使用不同的語法（也不使用VAR）了解不同的行為非常重要。如果您不理解并掌握自動(dòng)上下文轉(zhuǎn)換，您可能會(huì)花費(fèi)大量令人沮喪的時(shí)間來查看公式，而不能完全理解正在計(jì)算的值。
調(diào)用度量值時(shí)會(huì)自動(dòng)執(zhí)行上下文轉(zhuǎn)換，并且無法避免它。這意味著在調(diào)用度量值時(shí)避免上下文轉(zhuǎn)換的唯一方法是擴(kuò)展其代碼。例如，假設(shè)您以不同的方式編寫了以前的代碼，不是使用變量，而是定義一個(gè)名為AverageSales的度量值，它表示客戶的平均銷售額，如下面的代碼所示：
[AverageSales] := AVERAGEX ( Customer, [SumOfSalesAmount] )
[SalesMoreThanAverage] :=
SUMX (
Customer,
IF (
[SumOfSalesAmount] > [AverageSales],
[SumOfSalesAmount]
))
在突出顯示的行中，您使用[AverageSales]計(jì)算客戶的平均銷售額。問題是，這一次您調(diào)用的是迭代(SUMX)中的度量值，這使得上下文轉(zhuǎn)換發(fā)生。因此，[AverageSales]的結(jié)果不是所有客戶的平均銷售額，而是僅針對(duì)您正在迭代的客戶的平均銷售額。因此，測(cè)試將始終失敗并且度量值返回BLANK，因?yàn)镮F永遠(yuǎn)不會(huì)執(zhí)行真分支。如果要避免上下文轉(zhuǎn)換，則需要擴(kuò)展度量值的代碼，如以下示例所示：
[SalesMoreThanAverage] :=
SUMX (
Customer,
IF (
[SumOfSalesAmount] > AVERAGEX ( Customer, [SumOfSalesAmount]
),
[SumOfSalesAmount]
))
用擴(kuò)展代碼替換了度量值調(diào)用后，現(xiàn)在SalesMoreThanAverage返回正確的結(jié)果。此外，值得注意的是，在這種情況下，在Customer上有兩個(gè)嵌套的行上下文和三個(gè)度量值調(diào)用。其中兩個(gè)通過SUMX計(jì)算當(dāng)前迭代客戶的銷售額，另一個(gè)（AVERAGEX內(nèi)部）通過AVERAGEX計(jì)算當(dāng)前迭代客戶的銷售額。
我們將在下一章中廣泛使用這些特性，那時(shí)我們將開始編寫復(fù)雜的DAX代碼來解決特定的場(chǎng)景。
5.3.2 上下文轉(zhuǎn)換后多少行可見？
上下文轉(zhuǎn)換將行上下文轉(zhuǎn)換為等價(jià)的篩選上下文。
這一聲明需要澄清。行上下文始終包含單個(gè)行，而上下文轉(zhuǎn)換后CALCULATE創(chuàng)建的篩選上下文可能包含多行，而CALCULATE創(chuàng)建的篩選器可能會(huì)影響一個(gè)或多個(gè)列，具體取決于表結(jié)構(gòu)。
如果表具有在模型中定義的主鍵，則CALCULATE將創(chuàng)建僅篩選主鍵的篩選上下文。實(shí)際上，這樣的篩選上下文包含由主鍵唯一標(biāo)識(shí)的單個(gè)行。值得記住的是，可以通過使用表的元數(shù)據(jù)或通過創(chuàng)建將表作為目標(biāo)的關(guān)系來定義主鍵。在這兩種情況下，上下文轉(zhuǎn)換都將篩選單個(gè)列，因?yàn)樵摿惺潜淼臉?biāo)識(shí)，只有一行。
如果表沒有主鍵，則上下文轉(zhuǎn)換會(huì)在表的所有列上創(chuàng)建一個(gè)篩選器。這可能會(huì)導(dǎo)致包含一行或多行的篩選器，具體取決于表內(nèi)容。實(shí)際上，如果所有行都不同，則篩選上下文唯一地標(biāo)識(shí)一行。但是，如果表中有相同的行，則所有這些行都將包含在篩選上下文中。
在以下示例中，Wrong Sales和Correct Sales將返回不同的值：
[Sales Amount] := SUMX ( Sales, Sales[Quantity] * Sales[Unit
Price] )
[Wrong Sales] := SUMX ( Sales, [Sales Amount] )
[Correct Sales] := SUMX ( Sales, Sales[Quantity] * Sales[Unit
Price] )
實(shí)際上，Wrong Sales會(huì)對(duì)銷售進(jìn)行迭代，并且對(duì)于每一行，它會(huì)計(jì)算所有相同行的Sales Amount，而Correct Sales則計(jì)算每個(gè)單獨(dú)行的數(shù)量。因此，如果Sales中存在多個(gè)相同的行，則Wrong Sales會(huì)產(chǎn)生更高的值。
處理維度表時(shí)，這通常不是問題，因?yàn)榫S度總是具有主鍵。在這種情況下，唯一與當(dāng)前行相同的行是行本身。在事實(shí)表或一般沒有主鍵的表上，您需要考慮您可能有重復(fù)的行；否則你可能會(huì)得到意想不到的結(jié)果。
5.3.3 理解上下文轉(zhuǎn)換的評(píng)估順序
至此，您已經(jīng)了解了Product表中創(chuàng)建的這兩個(gè)計(jì)算列之間的區(qū)別：
Product[SumOfUnitPrice] = CALCULATE ( SUM ( Product[Unit
Price] ) )
Product[SumOfAllUnitPrice] = CALCULATE ( SUM ( Product[Unit
Price] ), ALL ( Product ) )
它們都是計(jì)算列，它們都使用CALCULATE函數(shù)。因此，兩者都發(fā)生了上下文轉(zhuǎn)換。
SumOfUnitPrice應(yīng)僅包含當(dāng)前行的Unit Price。但是，SumOfAllUnitPrice的值是多少？直觀地說，因?yàn)橛幸粋€(gè)ALL（Product），你應(yīng)該期望該列包含所有單價(jià)的總和。這正是它計(jì)算的內(nèi)容。然而，如果您按照我們到目前為止所描述的規(guī)則去做，您會(huì)發(fā)現(xiàn)存在錯(cuò)誤。
事實(shí)上，ALL（Product）返回整個(gè)Product表，有效地從篩選上下文中刪除任何篩選器。但是，與此同時(shí)，上下文轉(zhuǎn)換應(yīng)篩選產(chǎn)品并僅顯示一行。如果將這兩個(gè)條件放在AND中，則上下文轉(zhuǎn)換的限制性更大，因此它應(yīng)該獲勝。那么，為什么結(jié)果是所有單價(jià)的總和，而不是當(dāng)前的單價(jià)？
因?yàn)樯舷挛霓D(zhuǎn)換創(chuàng)建的篩選上下文與CALCULATE中的條件創(chuàng)建的篩選上下文之間存在優(yōu)先順序。CALCULATE先執(zhí)行上下文轉(zhuǎn)換，稍后應(yīng)用篩選器。因此，CALCULATE的任何條件都可以覆蓋由上下文轉(zhuǎn)換創(chuàng)建的篩選器。
描述這種行為比使用更難。實(shí)際上，上面的兩個(gè)公式可以直觀地計(jì)算出人們所期望的。然而，重要的是要理解為什么會(huì)發(fā)生這種情況，最重要的是，CALCULATE中的篩選器會(huì)覆蓋來自上下文轉(zhuǎn)換的篩選器（換句話說，篩選器參數(shù)將在后面應(yīng)用）。
5.4 變量和計(jì)算上下文
在第2章“介紹DAX”中，我們展示了變量的概念，它是通過使用以下表達(dá)式創(chuàng)建的：
VAR Denominator = SUMX ( Sales, Sales[SalesAmount] +
Sales[TotalProductCost] )
VAR Numerator = SUM ( Sales[SalesAmount] )
RETURN
DIVIDE ( Numerator, Denominator )
變量可以方便地使代碼更容易閱讀，并避免多次重復(fù)相同的子表達(dá)式，但由于它們使用計(jì)算上下文的方式，它們具有另一個(gè)非常重要的用途。事實(shí)上，DAX在您定義它們的計(jì)算上下文中計(jì)算變量，而不是在使用變量的上下文中。
這個(gè)特性對(duì)于復(fù)雜的公式非常有用，您希望根據(jù)以前的計(jì)算上下文引用值。讓我們看一個(gè)例子。想象一下，您希望使用數(shù)據(jù)透視表來顯示類別，并為每個(gè)類別顯示高銷售產(chǎn)品的數(shù)量。如果產(chǎn)品的銷售額超過該類別總銷售額的10％，則將產(chǎn)品定義為高銷售額。您可以在圖5-17中看到要實(shí)現(xiàn)的結(jié)果。
使用變量可以非常輕松地計(jì)算此度量值。此外，使用變量也使得閱讀更簡(jiǎn)單，如下面的代碼所示：
[HighSalesProducts] :=
VAR
TenPercentOfSales = [SalesAmount] * 0.1
RETURN
COUNTROWS (
FILTER (
Product,
[SalesAmount] >= TenPercentOfSales
))
該公式的有趣部分是DAX計(jì)算FILTER迭代之外的變量TenPercentOfSales。如果TenPercentOfSales的計(jì)算是在迭代內(nèi)部，那么由于上下文轉(zhuǎn)換，它將計(jì)算當(dāng)前迭代產(chǎn)品的10％的銷售額，從而使整個(gè)度量值計(jì)算失敗。相反，DAX計(jì)算迭代之外的值，然后在內(nèi)部使用它，從而可以引用當(dāng)前篩選上下文之外的表達(dá)式的值。如果你要編寫沒有變量的相同度量值，你應(yīng)該編寫一個(gè)表達(dá)式，如下所示：
[HighSalesProductsCalculate] :=
COUNTROWS (
FILTER (
Product,
[SalesAmount] >= CALCULATE (
[SalesAmount],
ALL ( Product ),
'Product Category'
) * 0.1
))
后一段代碼要難讀得多，因?yàn)榛旧?，您需要在迭代器?nèi)部重建先前的計(jì)算上下文，這不是一件容易的事，即使對(duì)于經(jīng)驗(yàn)豐富的DAX程序員也是如此。實(shí)際上，您將僅在第10章中了解前前面表達(dá)式的詳細(xì)信息，其中我們將揭示篩選上下文的所有詳細(xì)信息。
備注：
如果在閱讀上一個(gè)示例時(shí)，您發(fā)現(xiàn)可以使用變量來計(jì)算諸如EARLIER等計(jì)算上下文之類的內(nèi)容，那么我們很高興地歡迎您加入那些理解計(jì)算上下文的DAX程序員的精英。如果沒有，請(qǐng)不要擔(dān)心。在第一次閱讀本章時(shí)，它永遠(yuǎn)不會(huì)發(fā)生。書中還有很多內(nèi)容專門介紹這些復(fù)雜的主題，可以幫助您掌握計(jì)算上下文所需的技能。
5.5 理解循環(huán)依賴
在設(shè)計(jì)數(shù)據(jù)模型時(shí)，應(yīng)該注意一個(gè)復(fù)雜的主題，即公式中的循環(huán)依賴關(guān)系。在本節(jié)中，您將了解循環(huán)依賴關(guān)系以及如何在模型中避免它們。
在談到循環(huán)依賴之前，有必要討論簡(jiǎn)單的線性依賴關(guān)系。讓我們看一個(gè)例子，它包含以下計(jì)算列：
Product[Profit] = Product[Unit Price] - Product[Unit Cost]
新計(jì)算列取決于同一個(gè)表的兩列。在這種情況下，我們說列Profit取決于Unit Price和Unit Cost。然后，您可以使用以下公式創(chuàng)建一個(gè)新列，例如ProfitPct：
Product[ProfitPct] = Product[Profit] / Product[Unit Price]
很明顯，ProfitPct取決于利潤(rùn)和單價(jià)。因此，當(dāng)DAX計(jì)算表中的計(jì)算列時(shí)，它知道它將僅在Profit之后計(jì)算ProfitPct。否則，它將無法計(jì)算ProfitPct公式的有效值。
線性依賴關(guān)系通常不需要擔(dān)心。在內(nèi)部，DAX使用它來檢測(cè)數(shù)據(jù)模型刷新期間計(jì)算列的正確計(jì)算順序。在包含許多計(jì)算列的普通數(shù)據(jù)模型中，計(jì)算的依賴性變?yōu)閺?fù)雜的圖，同樣，引擎可以優(yōu)雅地處理這個(gè)圖。
循環(huán)依賴是在此圖中出現(xiàn)循環(huán)時(shí)發(fā)生的情況。例如，循環(huán)依賴關(guān)系出現(xiàn)的一個(gè)明顯情況是，如果您試圖將利潤(rùn)的定義修改為這個(gè)公式：
Product[Profit] := Product[ProfitPct] * Product[Unit Price]
因?yàn)镻rofitPct依賴于Profit，并且在這個(gè)新公式中，Profit取決于ProfitPct，DAX拒絕修改公式并顯示錯(cuò)誤“檢測(cè)到循環(huán)依賴”。
到目前為止，您已經(jīng)從公式的角度了解了什么是循環(huán)依賴。也就是說，在不注意表內(nèi)容的情況下，通過查看表達(dá)式檢測(cè)到依賴項(xiàng)的存在。然而，使用CALCULATE可以引入更微妙和復(fù)雜的依賴類型。讓我們通過一個(gè)示例來展示這個(gè)場(chǎng)景，從Product的一列子集開始，如圖5-18所示。請(qǐng)注意 ，在本例中 -，我們僅加載了Product表，從模型中刪除了所有其他表，以便使場(chǎng)景更加明顯。
我們有興趣了解使用CALCULATE函數(shù)的新計(jì)算列的依賴關(guān)系列表，如下所示：
Product[SumOfUnitPrice] = CALCULATE ( SUM ( Product[Unit
Price] ) )
乍一看，似乎該列僅取決于單價(jià)，因?yàn)檫@是公式中使用的唯一列。然而，我們使用CALCULATE將當(dāng)前行上下文轉(zhuǎn)換為篩選上下文。因?yàn)槲覀儧]有定義與表的任何關(guān)系，并且我們沒有為它設(shè)置主鍵，所以當(dāng)CALCULATE進(jìn)行上下文轉(zhuǎn)換時(shí)，它會(huì)篩選表的所有列。如果我們擴(kuò)展CALCULATE調(diào)用的含義，公式實(shí)際上是說：對(duì)Product表中與ProductKey，Product Name，Unit Cost和Unit Price具有相同值的所有行的單價(jià)的值相加。
如果您以這種方式閱讀公式，現(xiàn)在很清楚，代碼依賴于Product的所有列，因?yàn)樾乱氲暮Y選上下文將篩選表的所有列。您可以在圖5-19中看到結(jié)果表。
您可以嘗試在同一個(gè)表中使用完全相同的公式定義新的計(jì)算列。考慮使用以下公式定義NewSumOfUnitPrice，該公式與前一個(gè)公式相同。
Product[NewSumOfUnitPrice] = CALCULATE ( SUM ( Product[Unit
Price] ) )
令人驚訝的是，在這一點(diǎn)上，DAX提出了一個(gè)錯(cuò)誤，稱它檢測(cè)到循環(huán)依賴。奇怪的是，它用相同的代碼在公式中檢測(cè)到了以前沒有檢測(cè)到的循環(huán)依賴關(guān)系。確實(shí)發(fā)生了一些變化，它是表格中的列數(shù)。如果我們能夠?qū)ewSumOfUnitPrice添加到表中，我們將達(dá)到兩個(gè)公式具有以下含義的情況：
- SumOfUnitPrice對(duì)Product表中具有相同ProductKey值的所有行的Unit Price值求和，Product Name、Unit Cost、Unit Price和NewSumOfUnitPrice。
- NewSumOfUnitPrice對(duì)Product表中具有相同ProductKey、Product Name、Unit Cost、Unit Price和SumOfUnitPrice值的所有行的Unit Price值求和。
與表中的任何其他列一樣，計(jì)算列成為CALCULATE引入的篩選上下文的一部分，因此，所有計(jì)算列都是依賴項(xiàng)列表的一部分。如果您閱讀之前的定義，很明顯兩個(gè)公式之間存在循環(huán)依賴關(guān)系，這就是為什么DAX拒絕允許創(chuàng)建NewSumOfUnitPrice的原因。
理解這個(gè)錯(cuò)誤并不容易。然而，找到一個(gè)解決方案很簡(jiǎn)單，即使不是很直觀。問題是，如果表沒有主鍵，則任何包含CALCULATE的計(jì)算列（或?qū)θ魏味攘恐档恼{(diào)用，自動(dòng)添加CALCULATE）都會(huì)從表的所有列（包括計(jì)算列）創(chuàng)建依賴關(guān)系。如果表具有行標(biāo)識(shí)符（數(shù)據(jù)庫(kù)術(shù)語中的主鍵），則情況將有所不同。如果表具有充當(dāng)行標(biāo)識(shí)符的列，則包含CALCULATE函數(shù)的所有列將僅依賴于行標(biāo)識(shí)符，從而將其依賴性列表減少為單個(gè)列。
在Product中，ProductKey列可以唯一標(biāo)識(shí)每一行，因?yàn)樗侵麈I。為了將Product Key標(biāo)記為行標(biāo)識(shí)符，有兩個(gè)選項(xiàng)：
- 您可以使用ProductKey作為目標(biāo)列，從任何表創(chuàng)建到Product的關(guān)系。執(zhí)行此操作將確保ProductKey是Product的唯一值。
- 您可以使用“表行為”屬性手動(dòng)設(shè)置ProductKey列的行標(biāo)識(shí)符的屬性。
這些操作中的任何一個(gè)都告訴DAX引擎該表具有行標(biāo)識(shí)符。在這種情況下，您可以定義NewSumOfUnitPrice列以避免循環(huán)依賴，因?yàn)槭褂肅ALCULATE的兩個(gè)計(jì)算列將僅依賴于新鍵。
5.6 CALCULATE規(guī)則
回顧一下CALCULATE的工作方式很有用。您可以使用這組規(guī)則來測(cè)試您對(duì)CALCULATE的理解：如果您可以閱讀并理解所有這些規(guī)則，那么您就可以成為真正的DAX大師。
- CALCULATE和CALCULATETABLE是DAX中唯一直接操作篩選上下文的函數(shù)。
- CALCULATE只有一個(gè)必需參數(shù)，即要評(píng)估的表達(dá)式。其他參數(shù)（也稱為篩選器參數(shù)）是用于構(gòu)建新篩選上下文的篩選器；如果您只想調(diào)用CALCULATE來執(zhí)行上下文轉(zhuǎn)換，則可以省略它們。
- CALCULATE中的篩選器參數(shù)可以有三種形狀：
- （a）布爾條件，例如Product [Color] =“White”。
- （b）一列的值列表，以單列表的形式，如ALL（產(chǎn)品[顏色]）或更復(fù)雜的FILTER表達(dá)式，如FILTER（ALL（產(chǎn)品[顏色]，產(chǎn)品[顏色] = “白色”）。
- （c）一個(gè)表的行列表，如ALL（產(chǎn)品）或更復(fù)雜的FILTER條件，如FILTER（ALL（產(chǎn)品），Product [Color] =“White”）。
使用（a）或（b）編寫的條件只能在單個(gè)列上運(yùn)行。
使用（c）形狀寫入的條件可以在任意數(shù)量的列上工作。
- CALCULATE的所有篩選器參數(shù)都是獨(dú)立計(jì)算的，然后將它們放在邏輯AND中。最后，它們用于確定新創(chuàng)建的篩選上下文。
- CALCULATE的篩選器參數(shù)（從第二個(gè)參數(shù)開始）在原始篩選上下文中進(jìn)行計(jì)算，它們可以縮小、擴(kuò)展或替換計(jì)算的范圍。例如，當(dāng)使用直接布爾表達(dá)式作為參數(shù)時(shí)，CALCULATE將替換原始篩選上下文，而在傳遞表上使用FILTER的表達(dá)式時(shí)，CALCULATE會(huì)考慮原始篩選上下文。 CALCULATE的第一個(gè)參數(shù)（要評(píng)估的表達(dá)式）在新創(chuàng)建的篩選上下文中進(jìn)行計(jì)算。
- CALCULATE的上下文轉(zhuǎn)換和篩選器參數(shù)之間存在優(yōu)先順序。 CALCULATE在執(zhí)行上下文轉(zhuǎn)換后應(yīng)用篩選器。因此，篩選器可以覆蓋由轉(zhuǎn)換創(chuàng)建的上下文。
5.7 介紹ALLSELECT
當(dāng)您希望使用數(shù)據(jù)透視表中的頁面過濾器或切片器選擇作為參數(shù)之一來執(zhí)行計(jì)算時(shí)，ALLSELECTED是一個(gè)非常有用的函數(shù)。例如，想象一下，您想要構(gòu)建一個(gè)報(bào)告，如圖5-20所示。
在此報(bào)告中，您將顯示當(dāng)前行占列總數(shù)的百分比。使這個(gè)百分比難以計(jì)算的原因是產(chǎn)品顏色既用作切片器(在本例中，我們刪除了一些可用的顏色)，也用于行。如果你運(yùn)用目前學(xué)到的知識(shí)，你可以試試這個(gè)公式：
[SalesPct] :=
DIVIDE (
[Sales Amount],
CALCULATE (
[Sales Amount],
ALL ( Product[Color] )
))
使用ALL (Product[Color])可以從Color中刪除約束，并嘗試在列級(jí)別上計(jì)算總數(shù)。不幸的是，ALL刪除了所有約束，包括來自行和來自切片器的約束，從而導(dǎo)致錯(cuò)誤的百分比。在圖5-21中，您可以在數(shù)據(jù)透視表中查看公式的結(jié)果，其中總計(jì)不會(huì)顯示100％，而是顯示較小的值。
這里的問題是你計(jì)算所有顏色的分母，即使用戶只在切片器中選擇了一些顏色。對(duì)于每一行，您計(jì)算該行與分母的百分比，該分母大于數(shù)據(jù)透視表中顯示的總數(shù)。
這里需要的是一個(gè)函數(shù)，它不返回所有顏色，只返回在原始篩選上下文中選擇的顏色，即完整的主表之一。我們稱這種計(jì)算為可視總計(jì)，因?yàn)樗褂玫氖怯脩艨梢姷目傆?jì)，而不是完整數(shù)據(jù)模型的總計(jì)。這里使用的函數(shù)是ALLSELECTED。如果你這樣寫公式：
[SalesPct] :=
DIVIDE (
[Sales],
CALCULATE (
[Sales],
ALLSELECTED ( Product[Color] )
))
結(jié)果將是本節(jié)開頭所示的正確結(jié)果。
ALLSELECTED僅返回原始篩選上下文中可見的值，即數(shù)據(jù)透視表之一。換句話說，ALLSELECTED忽略數(shù)據(jù)透視表的行和列上的篩選器，并考慮用于計(jì)算總計(jì)的篩選器。
您可以使用三種不同類型的參數(shù)調(diào)用ALLSELECTED：
- 單列 在ALLSELECTED（Product [Color]）中，返回最初選擇的顏色。
- 全表 與在ALLSELECTED ( Product )中一樣，它將對(duì)表的所有列執(zhí)行ALLSELECTED操作，返回最初選擇的所有行。
- 沒有參數(shù) 您也可以使用沒有參數(shù)的ALLSELECTED（），它將對(duì)數(shù)據(jù)模型中的所有表執(zhí)行ALLSELECTED，從而可以計(jì)算數(shù)據(jù)透視表的總計(jì)，行和列上沒有篩選器。
您將主要使用ALLSELECTED以非常動(dòng)態(tài)的方式計(jì)算百分比和比率。在第10章中，我們將更深入地描述ALLSELECTED，它隱藏了一些復(fù)雜性，，我們將在后面討論高級(jí)評(píng)估上下文時(shí)討論這些復(fù)雜性。
5.8 理解 USERELATIONSHIP
CALCULATE提供的另一個(gè)功能是在計(jì)算表達(dá)式期間激活關(guān)系。實(shí)際上，正如您所知，數(shù)據(jù)模型可能包含活動(dòng)關(guān)系和非活動(dòng)關(guān)系。您可能在模型中具有非活動(dòng)關(guān)系，例如，您在兩個(gè)表之間存在許多關(guān)系，并且只能保留一個(gè)活動(dòng)關(guān)系。
例如，您可能在銷售表中存儲(chǔ)了每個(gè)訂單的訂單日期和交貨日期。通常，您希望根據(jù)訂單日期執(zhí)行銷售分析，但是，對(duì)于某些特定度量值，您要考慮交貨日期。在這種情況下，您在Sales和Date之間創(chuàng)建兩個(gè)關(guān)系：一個(gè)基于OrderDateKey，另一個(gè)基于DeliveryDateKey。一次只能激活其中一個(gè)，因?yàn)槟ǔ０从唵稳掌诜治鲣N售，所以您保持與OrderDateKey的關(guān)系處于激活狀態(tài)，而另一個(gè)處于非激活狀態(tài)。然后，您要?jiǎng)?chuàng)建一個(gè)度量值，該度量值顯示給定日期的交付值，以便將其與訂單值進(jìn)行比較。此新度量值（已交付金額）應(yīng)使用非活動(dòng)關(guān)系計(jì)算銷售額，同時(shí)停用與訂單日期的關(guān)系。
在這種情況下，您可以將CALCULATE與USERELATIONSHIP關(guān)鍵字一起使用，如下面的代碼所示：
[Delivered Amount] :=
CALCULATE (
[Sales Amount],
USERELATIONSHIP ( Sales[DeliveryDateKey], Date[DateKey] )
)
DeliveryDateKey和DateKey之間的關(guān)系將在[Sales Amount]的計(jì)算期間激活，同時(shí)，與OrderDateKey的關(guān)系將被停用。在圖5-22中，您可以看到一個(gè)數(shù)據(jù)透視表，顯示基于OrderDateKey的Sales Amount與新的Delivered Amount度量值之間的不同值。
使用USERELATIONSHIP激活關(guān)系時(shí)，您需要了解一個(gè)非常重要的方面：在調(diào)用表引用時(shí)定義關(guān)系，而不是在調(diào)用RELATED或其他關(guān)系函數(shù)時(shí)定義關(guān)系。
例如，如果要計(jì)算2007年交付的所有金額，則此公式將不起作用：
[Delivered Amount in 2007] :=
CALCULATE (
[Sales Amount],
FILTER (
Sales,
CALCULATE (
RELATED ( Date[CalendarYear] ),
USERELATIONSHIP ( Sales[DeliveryDateKey], Date[DateKey] )
) = 2007
)
)
原因是FILTER迭代Sales表（調(diào)用Sales表），然后計(jì)算條件。在計(jì)算條件期間，它會(huì)更改活動(dòng)關(guān)系，然后調(diào)用RELATED。但是，Sales和Date之間的關(guān)系已在調(diào)用Sales時(shí)定義，而不是在使用RELATED時(shí)定義。
如果要執(zhí)行上一個(gè)計(jì)算，則需要按以下方式重寫度量值：
[Delivered Amount in 2007] :=
CALCULATE (
[Sales Amount],
FILTER (
CALCULATETABLE (
Sales,
USERELATIONSHIP( Sales[DeliveryDateKey], Date[DateKey] )
),
RELATED ( Date[Calendar Year] ) = 2007
))
在后一個(gè)公式中，CALCULATE激活所需關(guān)系后調(diào)用Sales。因此，在FILTER內(nèi)部調(diào)用RELATED會(huì)發(fā)生與DeliveryDateKey活動(dòng)的關(guān)系。
這種行為使得在計(jì)算列表達(dá)式中使用非默認(rèn)關(guān)系成為一項(xiàng)復(fù)雜的操作，因?yàn)楸淼恼{(diào)用隱含在計(jì)算列定義中，因此您無法控制它，并且您無法使用CALCULATE和USERELATIONSHIP更改該行為。
