原文：How CALCULATE works in DAX
鏈接：http://sqlblog.com/blogs/marco_russo/archive/2010/01/03/how-calculate-works-in-dax.aspx
轉(zhuǎn)載：BI佐羅（bi@excel120.com）

注：計(jì)算字段與度量值為同意詞。原文對(duì)CALCULATE的基礎(chǔ)執(zhí)行已經(jīng)說(shuō)明得非常詳盡，這里進(jìn)行翻譯整理，以備參考。個(gè)人修改與添加內(nèi)容不再注明。

簡(jiǎn)介

CALCULATE是學(xué)習(xí)DAX（也就是學(xué)習(xí)PowerPivot，PowerBI，Excel數(shù)據(jù)建模）中最復(fù)雜最靈活最強(qiáng)大的函數(shù)，沒(méi)有之一。該文以實(shí)驗(yàn)的一步一步的方式揭開CALCULATE不同情景下的效果，如果你正對(duì)CALCULATE的詭異表現(xiàn)甚為困惑，那此文必讀。
舉個(gè)例子，在書（綠皮書）《微軟Excel 2013:用PowerPivot 建立數(shù)據(jù)模型》的第269頁(yè)，有一個(gè)關(guān)于實(shí)現(xiàn)YTD（年度截止到目前累計(jì)求和公式），如下：

Sales YTD =
IF (
    HASONEVALUE ( Calendar[Year] ),
    CALCULATE (
        SUM ( FactSales[SalesAmount] ),
        FILTER (
            ALL ( Calendar ),
            Calendar[FullDate] <= MAX ( Calendar[FullDate] )
                && Calendar[Year] = VALUES ( Calendar[Year] )
        )
    )
)

為了獲得累計(jì)效果，需要使用ALL獲取全部日期，但如此一來(lái)，MAX的參數(shù)不就是全部日期了嗎，那應(yīng)該得到12月31日，而實(shí)際中MAX卻表現(xiàn)正確，書中寫到MAX使用了篩選上下文，但這仍然無(wú)法解釋MAX的篩選上下文為什么沒(méi)有被ALL改變?yōu)槿咳掌?，而是?dāng)前透視表行標(biāo)簽對(duì)應(yīng)的日期所產(chǎn)生的篩選上下文。

CALCULATE涉及的幾個(gè)重要內(nèi)容需要深入理解，分別是：

1. CALCULATE的執(zhí)行順序
2. CALCULATE執(zhí)行時(shí)所處的篩選上下文范圍
3. CALCULATE的第一個(gè)參數(shù)特點(diǎn)
4. CALCULATE的其他參數(shù)（第一個(gè)參數(shù)以外）特點(diǎn)
5. CALCULATE中ALL的作用
6. CALCULATE中VALUES的作用
7. CALCULATE的上下文轉(zhuǎn)換作用
8. CALCULATE的篩選上下文自動(dòng)傳遞
9. CALCULATE在擴(kuò)展表作用下的表現(xiàn)

每一個(gè)度量值（計(jì)算字段）在計(jì)算時(shí)都涉及到上述所有問(wèn)題，你可能已經(jīng)學(xué)習(xí)了CALCULATE的寫法，但CALCULATE遠(yuǎn)比我們想象的強(qiáng)大和復(fù)雜。

測(cè)試一下對(duì)以下幾個(gè)例子的理解：

【案例一】

假設(shè)DimDate表有2556條記錄，則：

CountRows(DimDate)

返回結(jié)果：2556。

DimDate與FactSales有激活的關(guān)系存在，如下計(jì)算：

Calculate(CountRows(DimDate), FactSales)

返回結(jié)果：1096。思考為什么？答案是：擴(kuò)展表原理。

再看如下計(jì)算：

CALCULATE ( COUNTROWS ( DimDate ), ALL ( FactSales ) )

返回結(jié)果：2556。思考為什么？答案是：對(duì)ALL的作用之一：取消篩選的理解。

再看如下計(jì)算：

CALCULATE ( COUNTROWS ( DimDate ), FILTER ( ALL ( FactSales ), TRUE ) )

返回結(jié)果：1996。思考為什么？答案是：對(duì)ALL的作用之二：表函數(shù)的理解。

再看一個(gè)令人頭疼的例子：

DEFINE
    MEASURE Sales[Average Sales Amount] =
        AVERAGEX ( Sales, 'Sales'[Quantity] * 'Sales'[Net Price] )
EVALUATE
ADDCOLUMNS (
    VALUES ( Product[Color] ),
    "Sales", CALCULATE ( [Average Sales Amount], FILTER ( Sales, Sales[Quantity] > 3 ) )
)

參考：http://www.sqlbi.com/articles/context-transition-and-expanded-tables/

我們希望返回：不同顏色下，訂單中數(shù)量大于3的那些訂單平均銷售額?？山Y(jié)果卻是：

怎么會(huì)全部都是一樣的？請(qǐng)思考。
答案是：CALCULATE的上下文轉(zhuǎn)換及篩選傳遞與擴(kuò)展表的共同作用結(jié)果。

再看一個(gè)深度例子：

篇幅有限，請(qǐng)直接參考：
http://www.sqlbi.com/daxpuzzle/unexpected-filter-behavior-in-calculate/

完全想通了，那你已經(jīng)基本掌握CALCULATE計(jì)算背后各種可能遇到的坑。
暈了？沒(méi)關(guān)系。
《詳解CALCULATE系列》讓我們一起逐步了解CALCULATE及相關(guān)場(chǎng)景的每個(gè)細(xì)節(jié)。

本文先讓我們理解CALCULATE的基本工作原理。

原文

The CALCULATE function in DAX is the magic key for many calculations we can do in DAX. However, it is not pretty intuitive how it works and I spent a lot of time trying to understand how it can be used.
DAX的CALCULATE函數(shù)是很多應(yīng)用場(chǎng)合的關(guān)鍵。然而，它的工作原理遠(yuǎn)沒(méi)有看上去那么簡(jiǎn)單，需要花費(fèi)很多時(shí)間去理解它的用法。

First of all, this is the syntax.

CALCULATE( <expression>, <filter1>, <filter2>… )

The expression that we put in the first parameter has to be evaluated to return the result (that is a value, not a table). For this reason, the expression is usually an aggregation function like SUM, MIN, MAX, COUNTROWS and so on.
置于CALCULATE函數(shù)的第一個(gè)參數(shù)是為了運(yùn)算后返回結(jié)果（該結(jié)果是一個(gè)值而不是表），正是這個(gè)原因，該參數(shù)常常會(huì)用聚合函數(shù)如SUM, MIN, MAX, COUNTROWS等。

This expression is evaluated in a context that is modified by the filters in the following parameters. A key point is that these filters can both enlarge and restrict the current context of evaluation. Let’s try to understand what it means by considering a few examples. The following table is the one we import in PowerPivot in a table named Demo.
置于CALCULATE函數(shù)的第一個(gè)參數(shù)的表達(dá)式會(huì)基于CALCULATE函數(shù)隨后幾個(gè)篩選器參數(shù)所修改的上下文中計(jì)算，這些后面的篩選器參數(shù)的重要特性就是用來(lái)進(jìn)一步擴(kuò)大或進(jìn)一步限制計(jì)算的上下文。從下面一個(gè)Demo來(lái)理解，這是一個(gè)原始數(shù)據(jù)表：

If we project the count of Rows in a PivotTable putting the Name on the Rows, we get the following result:
當(dāng)使用透視表，將Name置于行標(biāo)簽，計(jì)算行數(shù)時(shí)，得到如下結(jié)果：

Now, we might have the need to calculate a measure which is not affected by the selection of the Name or, in other words, which always calculate the context for all the names. Thus, we define this calculated measure:
現(xiàn)在，如果需要計(jì)算總絕對(duì)行數(shù)，而不希望受到Name的影響，也就是說(shuō)希望計(jì)算的上下文總是所有的Name，定義如下的計(jì)算字段：

CountAllNames =
CALCULATE ( COUNTROWS ( 'Demo' ), ALL ( 'Demo'[Name] ) )

We obtain the CountAllNames column in the PivotTable that always returns the number of all the rows of the Demo table, without considering the filter on the Name.
將該計(jì)算字段添加進(jìn)透視表，可以看到，不受行標(biāo)簽處的Name的篩選，它總是返回所有的行數(shù)值，如下：

However, if we add Color attribute to the rows of the PivotTable, the CountAllNames is filtered by that attribute too. In fact, for each color, we have 2 rows for a Name (see Count of Rows column) and 6 rows considering all the names (see CountAllNames column).
如果此時(shí)添加Color屬性到透視表的行標(biāo)簽，計(jì)算字段CountAllNames又受到其影響了，觀察透視表，可以看出Count of Rows列標(biāo)黃色的單元格為2，這是因?yàn)閿?shù)據(jù)區(qū)對(duì)應(yīng)的2行被篩選出，同理 CountAllNames列標(biāo)綠色的單元格為6，這是因?yàn)閿?shù)據(jù)區(qū)對(duì)應(yīng)的6行被篩選出，如下所示：

Now, we can also add a Boolean expression as a filter of the CALCULATE function. For example, we might filter just the Car transport.
如果再添加一個(gè)對(duì)Car的篩選，寫出計(jì)算字段：

CountAllNamesCar =
CALCULATE (
    COUNTROWS ( 'Demo' ),
    ALL ( 'Demo'[Name] ),
    'Demo'[Transport] = "Car"
)

In this case, we will reduce the CountAllNamesCar column for a color of Emily to 3, because the number of rows with color Green and Car transport are 3.
這種情況下，觀察透視表的CountAllNamesCar列，可以看出每個(gè)單元格為3，這是因?yàn)閿?shù)據(jù)區(qū)對(duì)應(yīng)的標(biāo)綠色的3行被篩選出，也就是滿足Color與行標(biāo)簽一致，且transport屬性值為Car的行，如下所示：

At this point we might wonder whether the Calculate filter parameters are enlarging or restricting the context of evaluation. The rule is the following one.
通過(guò)上述幾個(gè)例子看出，Calculate函數(shù)的filter參數(shù)部分可以擴(kuò)大或縮小計(jì)算上下文的范圍使得計(jì)算結(jié)果不同，它滿足以下規(guī)則：

If the current context has a filter on a column of a PowerPivot table (which is a selection of a PivotTable, regardless it is a slicer, a report filter or a row/column selection), any reference for that column in one or more filter parameters of the Calculate function replaces the existing context. Then, the filters specified in the CALCULATE parameters are combined together like they were in an AND condition of a WHERE clause of a SQL SELECT statement.
如果透視表對(duì)數(shù)據(jù)列x存在一個(gè)篩選，這個(gè)篩選可能是切片器、行/列標(biāo)簽等（用戶在操作透視表時(shí)產(chǎn)生的篩選）；同時(shí)，Calculate函數(shù)內(nèi)直接存在對(duì)同一個(gè)數(shù)據(jù)列x的篩選參數(shù)，那么Calculate函數(shù)對(duì)數(shù)據(jù)列x的篩選將替換透視表已有的篩選。接著，該Calculate函數(shù)的這些篩選各自對(duì)應(yīng)的行集合進(jìn)行交集運(yùn)算便是最后結(jié)果。這類似SQL語(yǔ)句Select中WHERE子句中AND條件的效果。

For instance, consider a filter on the Color green using a Boolean expression in the CALCULATE function:
例如，考慮一個(gè)在Calculate函數(shù)直接使用布爾表達(dá)式篩選Color列為green的計(jì)算字段，如下標(biāo)藍(lán)色所示：

ColorGreen =
CALCULATE ( COUNTROWS ( 'Demo' ), 'Demo'[Color] = "Green" )

A Boolean expression used as a filter parameter in a CALCULATE function corresponds to an equivalent FILTER expression that operates on all the values of a column (for this reason, you can only a single column can be used in a Boolean expression that is used as a table filter expression):
CALCULATE函數(shù)內(nèi)直接使用布爾表達(dá)式作為篩選參數(shù)就等效于一個(gè)處理該列所有值的FILTER表達(dá)式效果（正是這個(gè)原因，作為Calculate函數(shù)的布爾表達(dá)式篩選參數(shù)表達(dá)式中只允許使用一列），其等效定義如下所示：

ColorGreen =
CALCULATE (
    COUNTROWS ( 'Demo' ),
    FILTER ( ALL ( 'Demo'[Color] ), 'Demo'[Color] = "Green" )
)

We obtain that ColorGreen column always filters by color Green and each Name has only 2 rows with color Green.
把ColorGreen放入透視表，結(jié)果是ColorGreen列總是篩選Color為Green的行數(shù)，所以每個(gè)位于ColorGreen列的單元值都是2。

Any filter expression in a CALCULATE statement overrides the existing selection of the PivotTable for the columns it contains. In the previous rule we highlighted the “reference” definition, because the FILTER that is internally used in place of the Boolean expression uses a FILTER expression that returns a set of values for the Color column. Thus, the existing selection for the color (the color is in fact specified in the rows of the PivotTable) is overridden by our filter and only green rows in the source table are considered to calculate the ColorGreen measure value. The true reason we lose the current selection on the color attribute is that the ALL( Demo[Color] ) expression returns a set of all the color values and ignores the existing selection.
如果CALCULATE函數(shù)內(nèi)有篩選表達(dá)式，且所篩選的列同樣在透視表也存在被篩選的情況，那CALCULATE函數(shù)內(nèi)有篩選表達(dá)式會(huì)覆蓋透視表的篩選。注意上述布爾表達(dá)式篩選和FILTER篩選器篩選，實(shí)際上，布爾表達(dá)式篩選的內(nèi)部實(shí)現(xiàn)正是FILTER（ALL（...）,...）篩選表達(dá)式。因此，對(duì)于這個(gè)例子的Color，雖然透視表存在對(duì)Color的篩選，但會(huì)被計(jì)算字段ColorGreen的Calculate中同樣對(duì)于Color的篩選所覆蓋，在覆蓋中，ALL( Demo[Color] )表達(dá)式返回了所有的行而忽略了透視表已經(jīng)存在的篩選。
If we don’t want to lose the existing selection of the PivotTable (that means that we don’t want to lose the existing filters on the calculation context), we can simply use in the FILTER expression a function that doesn’t ignore the existing selection. Instead of using the ALL( Demo[Color] ) expression as the source of the filter, we can use the VALUES( Demo[Color] ) expression, which keeps existing selections and returns the values still available in the color attribute.
In fact, if we use the following calculated measure:
如果不希望丟失透視表已經(jīng)存在的篩選，換句話說(shuō)，不希望丟失Calculate上下文中已有的篩選器，仍然可以使用FILTER表達(dá)式，并使用VALUES( Demo[Color] )子句替換ALL( Demo[Color] )子句，這樣就能保持對(duì)Demo[Color]列已有的篩選，如下所示：

ColorGreen =
CALCULATE (
    COUNTROWS ( 'Demo' ),
    FILTER ( VALUES ( 'Demo'[Color] ), 'Demo'[Color] = "Green" )
)

We obtain as a result that the Color filter in the PivotTable is still active, and returns no rows for all the colors but green.
此時(shí)透視表的ColorGreen列，除了Color為Green的行標(biāo)簽對(duì)應(yīng)的單元格仍然結(jié)果是2，其他單元格都是空。如下所示：

We can see that the FILTER expression in a CALCULATE function always replaces the previous context for the referenced columns. However, we can save the existing context by using an expression which uses the existing context and further restricts the members we want to consider for one or more columns. And this is what we have done using VALUES formula instead of ALL as the first parameter of the FILTER call.
Thanks to Marius Dumitru, the various combination of FILTER, ALL, VALUES in a CALCULATE statement can be summarized in this way.
也就是說(shuō)，位于CALCULATE函數(shù)中的FILTER表達(dá)式對(duì)其中指定列x的篩選總是會(huì)替換之前上下文對(duì)該列x的篩選，而且可以通過(guò)使用VALUES作為FILTER的第一個(gè)參數(shù)來(lái)保存之前上下文的篩選。CALCULATE的中FILTER，ALL，VALUES的組合應(yīng)用可總結(jié)如下：

CALCULATE (
    COUNTROWS ( 'Demo' ),
    FILTER ( ALL ( 'Demo'[Color] ), 'Demo'[Color] = "Green" )
)

ignores/replaces existing Color filters and sets a filter on Green
這將忽略/替換已存在的顏色篩選器并重新設(shè)置一個(gè)Color為Green的篩選器。

CALCULATE (
    COUNTROWS ( 'Demo' ),
    FILTER ( VALUES ( 'Demo'[Color] ), 'Demo'[Color] = "Green" )
)

keeps existing Color filters and adds a further filter on Green
這將保持已存在的對(duì)Demo[Color]列的篩選，并對(duì)此列增加一個(gè)Color為Green的篩選器。

CALCULATE (
    COUNTROWS ( 'Demo' ),
    FILTER ( ALL ( 'Demo'[Color] ), 'Demo'[Color] = "Green" ),
    VALUES ( 'Demo'[Color] )
)

same as the previous expression (keeps existing Color filters) NOTE: the first filter would consider all the colors, but the second expression (VALUES) only consider the current selection and the two filters will be considered using an AND condition, thus.
與上一個(gè)例子類似， FILTER( ALL('Demo'[Color]), 'Demo'[Color] = "Green" )重置了一個(gè)Color為Green的篩選器，VALUES('Demo'[Color])保持了已有的篩選器，整個(gè)篩選結(jié)果是這兩個(gè)篩選器的綜合效果，即同時(shí)滿足兩處篩選的那些行。

CALCULATE (
    COUNTROWS ( 'Demo' ),
    FILTER ( ALL ( 'Demo' ), 'Demo'[Color] = "Green" )
)

ignores/replaces filters on all Demo columns, and sets a filter on all columns (Name, Transport etc., not just Color) with rows that meet the condition.
注意這里是：ALL('Demo')而不是ALL('Demo'[Color])，即重置對(duì)于'Demo'的所有列篩選器，其中'Demo'[Color] = "Green"，在這種情況下選擇符合篩選的所有行。

Finally, some words of caution have to be spent to the first parameter we pass to the FILTER function. If we consider this expression:
FILTER的第一個(gè)參數(shù)很重要，再舉一個(gè)列子如下所示：

ColorGreen =
CALCULATE ( COUNTROWS ( 'Demo' ), FILTER ( 'Demo', 'Demo'[Color] = "Green" ) )

We pass the whole Demo table to the FILTER condition, which results a filter of the current context with all the columns! In this way we apply a restrictions on the color green and we get the same result as before (no rows for all the colors but green, the selection of color of the PivotTable is still applied) but, remember, the FILTER is returning ALL the rows. What does it mean?
在FILTER第一個(gè)參數(shù)使用整個(gè)Demo表，這將使用與Demo表當(dāng)前篩選上下文完全一致的篩選上下文，在此基礎(chǔ)上，重設(shè)對(duì)'Demo'[Color]列的篩選為Green，雖然整個(gè)計(jì)算字段ColorGreen的效果與之前是一致的，即除了Color為Green的單元格值為2，其他該列的單元格值為空，但不同的是FILTER在這里返回了所有的列，而不僅僅是'Demo'[Color]這列了。

Well, consider a further selection on the PivotTable where the Transportation attribute is filtered by Bike. This is the result using the ColorGreen definition we have just defined.
為了更充分地理解上面這個(gè)FILTER返回單列和多列的不同，對(duì)透視表增加一個(gè)Transportation 為Bike的報(bào)表篩選，此時(shí)在剛剛定義的計(jì)算字段ColorGreen列對(duì)應(yīng)的值處可以看出變化，由于FILTER是在與'Demo'當(dāng)前完全一致的篩選上下文（也就是攜帶著Transportation 為Bike的報(bào)表篩選）基礎(chǔ)上再做'Demo'[Color] = "Green"的進(jìn)一步篩選，對(duì)于行標(biāo)簽為Green處符合篩選的只有一行，透視表單元格值為1，標(biāo)為綠色，如下所示：

Now, let’s add another filter to the CALCULATE function, filtering also the rows with Trasport equals to Car.
所有透視表的情況不變，再進(jìn)一步修改計(jì)算字段CarGreen如下：

CarGreen =
CALCULATE (
    COUNTROWS ( 'Demo' ),
    FILTER ( 'Demo', 'Demo'[Color] = "Green" ),
    'Demo'[Transport] = "Car"
)

which, as we have seen before, corresponds to:
如之前所述，CALCULATE篩選參數(shù)形如“表[列]=值”的布爾表達(dá)式等效于“FILTER(ALL(表[列]),表[列]=值)”，即：

CarGreen =
CALCULATE (
    COUNTROWS ( 'Demo' ),
    FILTER ( 'Demo', 'Demo'[Color] = "Green" ),
    FILTER ( ALL ( 'Demo'[Transport] ), 'Demo'[Transport] = "Car" )
)

The results is that the filter for Bike defined in the PivotTable plus the filter for Car defined in the CarGreen measure returns no rows at all in the CarGreen result. If we think about it, why this happens is not very intuitive at this point!
結(jié)果是兩個(gè)FILTER的結(jié)果的交集，第一個(gè)FILTER使用已存在的篩選上下文，包括行/列標(biāo)簽處即報(bào)表篩選器，并覆蓋替換'Demo'[Color]列的篩選器為"Green"，選出符合的行；第二個(gè)FILTER只是針對(duì)'Demo'[Transport]列，覆蓋替換'Demo'[Transport]列的篩選器為"Car"，選出符合的行；這時(shí)對(duì)于計(jì)算字段CarGreen的每個(gè)單元格值都是空。這點(diǎn)只有嚴(yán)格根據(jù)已知的規(guī)則去計(jì)算才會(huì)明確知道結(jié)果，已不是單從表面就能看出來(lái)的了，如下所示：

Our filter on the Transport column is actually restricting the current selection and is not replacing it! But, wait, if we write just the filter on Transport, without the filter on Color, we have:
值得注意和強(qiáng)調(diào)的關(guān)鍵是：第二個(gè)FILTER對(duì)于Transport列并非替換了當(dāng)前篩選上下文，從Bike替換為Car，而只是在這個(gè)FILTER里將當(dāng)前篩選上下文對(duì)于Transport列重新做了限定，限定為Car。如果去掉第一個(gè)對(duì)于Color的篩選器，如下所示：

TransportCar =
CALCULATE ( COUNTROWS ( 'Demo' ), 'Demo'[Transport] = "Car" )

that produces the following result, which replaces the Bike selection of the PivotTable in the TransportCar column!
這將Transport列重新做了限定，限定為Car，這種重新限定也可以說(shuō)是替換了報(bào)表篩選器對(duì)Transport列的篩選，如下所示：

This last calculation (TransportCar) is simple to explain, because it has the same behavior we have seen before with the first ColorGreen calculated measure, where the filter of the color green replaced any existing color selection in the PivotTable. The difference in the CarGreen calculation is the other filter parameter, which returns all the columns from the Demo table. Consider the highlighted filter in the CarGreen formula:
這個(gè)計(jì)算字段TransportCar很容易解釋，但和之前定義的計(jì)算字段ColorGreen 的解釋不同的是，以表'Demo'作為FILTER的第一個(gè)參數(shù)時(shí)，返回的是所有的列：

CarGreen =
CALCULATE (
    COUNTROWS ( 'Demo' ),
    FILTER ( 'Demo', 'Demo'[Color] = "Green" ),
    FILTER ( ALL ( 'Demo'[Transport] ), 'Demo'[Transport] = "Car" )
)

The filter on color green returns all the columns of the current context. If we consider the corresponding rows for the cell B5 of the PivotTable (Emily, Green, Bike), this is just one row (the yellow one), and this row has the Bike value for the Transport attribute. When we apply the second filter, we have a single value for the attribute Transport, which is Car. At this point, the intersection between those two sets of Transport (one is only Bike, the other is only Car) is an empty set. Thus, the result for CarGreen measure is empty, because there are no corresponding rows for the selection made.
第一個(gè)FILTER返回了基于當(dāng)前篩選上下文的所有列，如以上的透視表所示，如果考慮位于B5的單元格，被透視表篩選限定為(Emily, Green, Bike)，這對(duì)應(yīng)于計(jì)算字段Count of Rows的單元格，值為1，即只有一行同時(shí)滿足篩選；再考慮第二個(gè)FILTER，返回Transport列值為Car的行；同時(shí)考慮兩個(gè)FILTER，它們結(jié)果的交集是空。值得注意和強(qiáng)調(diào)的是：第二個(gè)FILTER對(duì)于Transport列并非替換了當(dāng)前篩選上下文，從Bike替換為Car，而只是單純地基于Filter的規(guī)則進(jìn)行計(jì)算。

This can be tricky, but we finally have this behavior.

• The CALCULATE function applies a calculation (the first parameter) for each cell, considering the resulting context by applying the filters (the second and following parameters) to the current context.
• Each filter can have values for one or more columns.
• Each column is computed individually in the filters expressions of the CALCULATE function
• If a column value is specified in at least one filter, it replaces the selection of the current context for that column.
• If a filter expression returns more columns, each one has its own independent set of values in the final calculation context definition
• If a column is specified in more filters, the resulting values are the intersection of these set of values (for that column).
• After all the filters have been evaluated, the intersection of the column values determines the calculation context for the expression passed as the first parameter to the CALCULATE function
  Despite its complexity, this calculation is pretty fast. The key point is to understand all the side effects we have when a filter returns more columns than those we specified in the filter condition itself, which is something we have to consider carefully each time we use one or more FILTER functions inside a CALCULATE expression.

最終總結(jié)以上所有行為的規(guī)律如下：

• CALCULATE函數(shù)基于當(dāng)前上下文應(yīng)用第二個(gè)參數(shù)起所有篩選器對(duì)(計(jì)算字段)每個(gè)單元格使用第一個(gè)參數(shù)進(jìn)行計(jì)算。
• 每個(gè)篩選器可以是對(duì)單列或多列的。
• 每列都在CALCULATE函數(shù)的等效FILTER表達(dá)式中獨(dú)立計(jì)算。
• 如果CALCULATE函數(shù)的等效FILTER表達(dá)式對(duì)某列存在篩選，這將覆蓋/替換當(dāng)前篩選上下文位于該列的篩選器。
• 如果等效FILTER表達(dá)式返回多列，每一列都有獨(dú)立的結(jié)果。
• 如果某列在不同的等效FILTER表達(dá)式結(jié)果中均有返回，該列的最終篩選結(jié)果是交集。
• 在所有等效FILTER計(jì)算后，這些被篩選的列的交集形成了CALCULATE第一個(gè)參數(shù)計(jì)算的上下文。
  這種復(fù)雜機(jī)制是為了確保計(jì)算的效率，關(guān)鍵是要搞清楚可能對(duì)CALCULATE計(jì)算上下文產(chǎn)生影響的各個(gè)方面，尤其是置于CALCULATE函數(shù)中的諸多FILTER的共同作用。

后記

考慮開篇給出的案例作為收尾：

YTD =
IF (
    HASONEVALUE ( Calendar[Year] ),
    CALCULATE (
        SUM ( FactSales[SalesAmount] ),
        FILTER (
            ALL ( Calendar ),
            Calendar[FullDate] <= MAX ( Calendar[FullDate] )
                && Calendar[Year] = VALUES ( Calendar[Year] )
        )
    )
)

ALL( Calendar )對(duì)Calendar做了重新限定，F(xiàn)ILTER將對(duì)Calendar所有的行做迭代返回<=MAX(Calendar[FullDate])的行集合，而關(guān)鍵是MAX進(jìn)行運(yùn)算的上下文是什么，是透視表產(chǎn)生的上下文還是ALL( Calendar )，如果是透視表產(chǎn)生的上下文，將根據(jù)不同行標(biāo)簽得到不同的值，如果是ALL( Calendar )，總會(huì)得到最大日期，現(xiàn)實(shí)情況是運(yùn)算是符合預(yù)期的正確的，也就是前者，而不是ALL( Calendar )。關(guān)鍵在于正文中重復(fù)了多次的關(guān)鍵：FILTER對(duì)于表/列并非替換了當(dāng)前篩選上下文，而只是在這個(gè)FILTER里將當(dāng)前篩選上下文對(duì)于表/列重新做了限定。MAX使用的當(dāng)前上下文并沒(méi)有變化。值得進(jìn)一步揭示的是：FILTER對(duì)于表/列并非替換了當(dāng)前篩選上下文，而只是單純地根據(jù)FILTER的規(guī)則進(jìn)行計(jì)算。
為了更好地理解帶有ALL的問(wèn)題，我們需要在后續(xù)的文章中進(jìn)一步討論。