第39式 CALCULATE 的列表筛选（高级）
衔接上一部分第39式。参阅《DAX圣经》第10章，《扩展表系列》
1、元组（值列表）和列表筛选
这一部分，我们将陈述列表筛选的最终定义。
在这之前，请预先观看本简书中的《扩展表》系列，以便更好的衔接上本部分中的知识。如果你对使用扩展表及其在DAX中的工作方式已有信心，那么，现在是时候对计算筛选的交互进行更深入的分析，并编写计算筛选的最终定义了。
1）仔细回忆一下，我们之前所说的筛选，基本上涉及的都是单列表筛选。也简单介绍了如何通过函数引用列表或定义值列表，以及它们之间的区别与规则，但是，总感觉还没有形成统一的比较系统的概念。
我们对于引用列表应该很熟悉了，即使用需要的功能函数，然后引用一个列表即可。例如：=SUM（Sales[销售]），SUM函数引用Sales表中的[销售]列进行聚合计算（执行SUM函数的功能）。
实际上，大多数情况下，引用列表、定义值列表，以及值列表与列表如何组合来创建不同的DAX筛选计算，才是关键与重点。
根据前面我们关于值列表的定义：值列表是由列表全集的部分子集构成，也就是说，它由部分列值构成。你也可以称之为元组（一个值列表单元，可能存在多个元组构成值列表的情况，两者还是有细微的差异。为了方便，大多数情况下我们使用值列表）。换句话说，元组是一组列值（一个或多个列值构成的单元）。
我们知道，DAX内部引擎能识别和执行的只有列表，值列表只是列表的一种存在方式与状态。任何值列表或者说你定义一个值列表的目的只有一个，直观而简单的说：
元组（值列表）在数据模型上充当筛选器！
所以，你不能引用一个列表来做为筛选器。有人说，我看见过使用列表作为筛选器的情况，比如直接使用事实表Sales表（如多对多关系中），这其实是在扩展表条件下：Sales列表在本身Sales表中属于列表状态，但在整个扩展表（扩大的Sales表）中，Sales表的任何一个列表同时又只是该扩展表的部分列值，即值列表。相当于还是定义值列表筛选，这在后面将要详述。
我们对于如何定义值列表其实并不陌生， 例如，在Date表中，元组（值列表）可能是：
« Year = 2018 -- 由所有年份等于2018的列值构成
Month = January » 等等
你还可以在Date表所在的数据模型表里定义无数个这样的元组（值列表）。
你可以将值列表理解为：能用于筛选列表的单元。
2）列表中的值列表（元组）可能属于不同的表。可以将元组（值列表）看作是包含来自相同或不同表的列值，即在数据模型里，定义列表模型的某些列的列值。通俗的讲：你可以从数据模型里的一个或多个列表里取出一个或多个列值构成值列表。反过来说，列表由不同的一个或多个列值的元组（值列表）构成。
例如，应用于包含在sales表模型的January 2007 年1月(用于筛选的单元)，将筛选出所有包含2007年1月的Sales表。要查看用作筛选器的元组(值列表)示例，请查看以下表达式：
CALCULATE (……
FILTER(
CROSSJOIN ( ALL ( Date[Year] ), ALL ( Date[Month] ))，
OR( AND ( Date[Year] = 2007, Date[Month] = "January" )，
AND ( Date[Year] = 2006, Date[Month] = "December" ))))
CALCULATE的FILTER参数其实是以下元组--值列表：
« Year=2006，
Month=December »
« Year=2007，
Month=January »
注意：也可以将上述四个元组值列表，写成两个：
1） AND ( Date[Year] = 2006, Date[Month] = "December" )或
Date[Year] = 2006 && Date[Month] = "December"
2） AND ( Date[Year] = 2007, Date[Month] = "January" )或
Date[Year] = 2007 && Date[Month] = "January" )
值列表是一个筛选器，但是它还不是当前的计算筛选。当前计算筛选实际上是一组筛选器。实际上，如果我们查看以下代码：
CALCULATE (……
FILTER(
CROSSJOIN (ALL ( Date[Year] )， ALL ( Date[Month] ))，
OR(
AND ( Date[Year] = 2007， Date[Month] = "January" )，
AND ( Date[Year] = 2006， Date[Month] = "December" )))，
OR ( Product[Color] = "Black"， Product[Color] = "Yellow" )
生成的列表筛选器包含两组不同的两个值列表--元组：
1）OR( AND ( Date[Year] = 2007， Date[Month] = "January" )，
AND ( Date[Year] = 2006， Date[Month] = "December" )))
2）OR ( Product[Color] = "Black"， Product[Color] = "Yellow" )
第一组用于从日期表中筛选出两列，另一组用于筛选产品颜色。
因为在数据模型里，筛选器与表相同。为了使筛选器和表之间有一个明显的区别，每当表示筛选器时，我们就使用一个带筛选器的图标来表示它。例如由前面公式产生的筛选器在图中的表示：
如图中的一组筛选器，是一个当前值列表筛选（引擎在执行时将其作为列表）。因此，值列表筛选非常类似于一组列表。我们说：
如果一个列定义为某个筛选器（值列表），该列就会被当前筛选器筛选。因此，在示例中，年份、月份以及颜色三列属于被筛选的列表。乍一看，元组值列表看起来更像一个个普通的行，一旦将它视为筛选器时，它看起来更像列表。
事实上，我们多次提到：这两个概念是非常相似的，只是因为在使用时需要将其作为两种不同的列表形态。
例如，在前面的代码中，筛选器的结果是一个表，只有将它用作CALCULATE（）的参数时，它才会成为值列表筛选。然后DAX通过CALCULATE（）将其转换为列表筛选，它的各个行则变成元组（一个个的列值）。这种区别是微妙的，但是我们发现它们在描述列表筛选的行为时非常有用。因此，我们更倾向于使用不同的名称来理解及定义它们！
例如：将某个真实的列表做为筛选器与通过CROSSJOIN()的结果列表转换成筛选器，它们之间是有很大的区别。
1) 当使用CROSSJOIN()作为列表筛选时，筛选列表包含CROSSJOIN()产生的各个列；
2）而当使用一个完整的表作为列表筛选时，列表筛选将包含原始表所在扩展表的所有列。
如果查看以下两个表达式，它们中使用的筛选器参数具有不同的元组(部分列值)结果：
1) CALCULATE (SUM ( Sales[Quantity] ),
'Product Subcategory'[Subcategory] = "Radio")
2) CALCULATE (SUM ( Sales[Quantity] ),
FILTER ('Product Subcategory'，'Product Subcategory'[Subcategory] = "Radio"))
实际上，第一个筛选参数生成一个由元组(部分列值)组成的CALCULATE ()筛选，其中有一个列： [Subcategory = “Radio” ]。
另外，第二个表达式引用'Product Subcategory'表上的FILTER()筛选器。通过这种方式，生成的筛选器筛选包含'Product Subcategory'扩展表的所有列的元组：
«'Product Subcategory'[Subcategory] = Radio，
'Product Subcategory'[ProductSubcategoryKey] = 3，
'Product Subcategory'[ProductCategoryKey] = 1，
'Product Category'[ProductCategoryKey] = 1，
'Product Category'[Category] = Audio »
正如我们在本章中所了解的，这种差异非常重要，这可能是使用表作为筛选器时容易产生误解的原因。这就是我们要求你预先观看《扩展表系列》内容的原因。
现在，我们已经了解了元组和列表筛选是什么，是时候定义DAX在筛选器筛选中使用的两个操作符的时候了。DAX的筛选使用的两个操作方式是：
INTERSECTION 和 OVERWRITE --相交（交集）和覆盖。
2、筛选的交集（INTERSECTION）
假如定义两个筛选A和B，A和B的交集是通过将A中的筛选器加到B中的CALCULATE()筛选器中。当A和B的筛选器参数需要合并时，通过求交集实现。一些例子对澄清这一概念非常有用。查看以下表达式：
CALCULATETABLE (，，，
Date[Year] = 2008，OR (Product[Color] = "Black"， Product[Color] = "Yellow"))
这里很显然有两个筛选器，一个在[Year]列上，另一个在[Color]列上。由于CALCULATE的筛选器参数总是“并”的关系（与AND结合在一起），但现在我们讨论的是元组值列表，所以，可以更具体地说：它们是（AND）交集结合在一起的。实际上，筛选器筛选结果在下图中是可见的：
图中两个筛选器的交集是筛选器的AND--联合，也就是说，它们之间是逻辑关联的。
交集是一个非常简单的操作，也就是说，可以将交集看作是由筛选器定义的列表之间AND连接的简单操作。
此外，一个特别需要注意的地方：每个交集的交接处的特点，它可以很好地处理复杂的筛选器。实际上，如果查看下面的表达式，我们将注意到日期表被两个条件筛选，使用的模式完全不同。
第一个条件筛选器：2007年1月和2006年12月：
第二个条件筛选器：筛选出销售量大于100对应的时期(不论年份和月份)。
然而，每一个交集执行了一个正确的计算(2007年1月和2006年12月，都仅在销售数量大于100这个给定日期天数的条件下才会显示)。
CALCULATE (，，，
FILTER(
CROSSJOIN (ALL ( Date[Year] ), ALL ( Date[Month] ))，
OR(AND ( Date[Year] = 2007, Date[Month] = "January" )，
AND ( Date[Year] = 2006; Date[Month] = "December" )))，
FILTER (ALL ( Date[Date] ), CALCULATE ( SUM (Sales[Quantity] ) ) > 100)))
最后，交集是对称的：A与B相交的结果同B与A相交的结果相同，这是预期的结果。但正如我们在下一节中所了解的：overwrite--覆盖运算符则是不对称的，你需要更加小心地使用它。
3、筛选的覆盖（OVERWRITE）
在了解了交集操作后，现在介绍DAX可以在筛选器上执行的第二个操作：OVERWRITE--覆盖。OVERWRITE是在CALCULATE(0里使用的运算符，它将新的筛选器筛选参数与前一个筛选器筛选合并来计算，从而创建新的当前计算筛选提供给表达式计算。
例如，查看以下表达式：
CALCULATE(
CALCULATE (,,,,Product[Color] = "Yellow")，
Product[Color] = "Black")
内部CALCULATE (,,,,Product[Color] = "Yellow")首先筛选计算，这将覆盖前一个筛选器，即DAX引用[Color]列表的Product[Color] = "Yellow"--只包含黄色的行的值列表作为筛选，该值列表筛选下的结果表又被第一个CALCULATE()引用为第一参数：计算列表。
请注意：这时候的计算列表是一个只包含Yellow--黄色的行的表。所以，这时候的B--值列表筛选器：Product[Color] = "Black"将被清理为空（相当于没有筛选或不起作用：因为计算列表是所有包含"Yellow"的行列值的表，再用一个包含"Black"的列值的值列表去筛选它，怎么有作用？计算列表里根本不包含"Black"的列值）。
我们知道这是预期的结果，但它表明：在这种情况下，两个筛选器筛选不是使用交集组合，而是使用OVERWRITE--覆盖运算符。所以，实际上，DAX计算"Yellow"--黄色的结果，而忽略Product[Color] = "Black"上的筛选器（不起作用）。
为了说明问题，在前面的示例中，B筛选器包含 "Black"--黑色。下图中，可以看到覆盖操作的图形表示：
OVERWRITE--覆盖的定义很简单，但是在DAX公式的使用中，当具有特殊筛选器时会导致非常复杂的场景。
让我们从它的定义开始：在CALCULATE里的A覆盖B时，使用B表示先前的筛选器筛选，将A用于覆盖B的筛选器筛选，这样就可以更容易地读取句子“A覆盖B”，因为新的筛选器(A)覆盖了旧的筛选器（B）。在前面的例子中，A是"Yellow"的，B是 "Black"-的。为了计算A覆盖B，DAX执行两个操作：
1）从B中的所有筛选器中移除在A中筛选的列，生成一个新的筛选器筛选，我们称之为B- Cleaned 清理；
2）B- Cleaned与A相交。
在前面的示例中，B筛选包含 "Black"，B- Cleaned将变为空，因为DAX删除了唯一存在的列(Color-颜色，因该列在A筛选中存在)，并最终生成仅包含"Yellow"-黄色筛选器的新列表筛选器。
OVERWRITE--覆盖是一个强大的运算符，但它需要更多的关注。事实上，只要筛选器是良好的形状，它就会以直观的方式工作，但对于任意形状的筛选器，它开始变得更加复杂。在继续描述之前，现在是时候引入任意形状的筛选器了，因为它们在解释DAX中起着重要的作用。
第40式 CALCULATE 的任意形状筛选器
1、形状良好的筛选器
所谓形状良好的筛选器是指：可以表示为单列筛选器，CROSSJOIN--交叉连接的筛选器等。形状良好的筛选器的例子如下：
CALCULATETABLE (,,,
OR(Date[Year] = 2007，Date[Year] = 2006)，
OR (Product[Color] = "Black"， Product[Color] = "Yellow"))
实际上，前一个筛选器与下面这个筛选器等价：
CALCULATETABLE (,,,
CROSSJOIN(
FILTER (ALL ( Date[Year] )，
OR(Date[Year] = 2007，Date[Year] = 2006))， FILTER(ALL (Product[Color] )，
OR (Product[Color] = "Black"， Product[Color] ="Yellow"))))
我们可以很容易地将形状良好的筛选器表示为在单个列上的一组筛选器，如图所示：
形状良好的筛选器总是导致简单的交集和覆盖工作，并以直观的方式与良好的形状筛选器存在。
2、任意形状的筛选器
任意形状的筛选器则是形状不佳的筛选器。换句话说，不能将任意形状的筛选器表示为单个列上的筛选器的CROSSJOIN（一般为左连接）。我们以前就使用过任意形状的筛选器，这在下面的表达式中可以看到：
CALCULATE (,,,
FILTER(
CROSSJOIN (VALUES ( Date[Year] )， VALUES ( Date[Month] ))， OR (AND ( Date[Year] = 2007， Date[Month] = "January" )， AND ( Date[Year] = 2006， Date[Month] = "December" ))))
此筛选器的结果如图所示，如我们所见。任意形状的筛选器不是简单列筛选器的CROSSJOIN。
不能将筛选器定义为两个单独的列。相反，需要将列放在同一个列表集中，因为它们具有存储在筛选器本身中的关系。
我们不能将包含2007中的一个月和2006中的一个月的筛选器表示为简单列表筛选的CROSSJOIN—交集连接。实际上，DAX只能通过将两列放在一起来计算筛选器。也就是说： 任意形状的筛选器定义了其所在列之间的关系。
事实上，在学术论文中称筛选器(或更一般的称为：表)为列表关系。我们倾向于避免在书中使用这个术语，因为它与更广泛使用的列表关系之间的概念相冲突。然而，一个表(在本例中是一个筛选器)定义了列之间的关系。
任意形状的筛选器问题，可以看成是一种覆盖语义。事实上，尽管交集对于任意形状的筛选器非常好，但是覆盖会导致更复杂的场景。例如，让我们以前面的表达式为例，在年份中添加另一个条件，如下所示：
CALCULATE (,,,
FILTER (
CROSSJOIN (VALUES ( Date[Year] )， VALUES ( Date[Month] ))，
OR (AND ( Date[Year] = 2007， Date[Month] = "January" )，
AND ( Date[Year] = 2006， Date[Month] = "December" )))，Date[Year] = 2007)
如我们所料，这个表达式计算2007月份的值，因为第一个筛选器返回2007年"January"-1月，与第二个筛选器 2006年 "December"-12月相交，其结果为仅返回2007年1月。在下图中，我们可以看到交集后的图形表示形式：
任意形状的筛选器在交集模式下工作得很好，如果用这种方式编写同样的表达式，情况就会不同：
CALCULATE (
CALCULATE (,,,, Date[Year] = 2007)，
FILTER (CROSSJOIN (VALUES ( Date[Year] )， VALUES ( Date[Month] ))， OR (AND ( Date[Year] = 2007， Date[Month] = "January" )， AND ( Date[Year] = 2006， Date[Month] = "December" )))
在这个表达式中，内部CALCULATE (,,,,Date[Year] = 2007)表，是外层CALCULATE()的第一参数--计算表，该[Year]=2007值列表是通过CALCULATE()执行列表转换的结果。前一个版本和后一个版本公式之间的区别是：现在的CALCULATE使用了 OVERWRITE-覆盖来合并这两个筛选器。覆盖从第一个筛选器中删除Date[Year] ，如是，筛选器里留下 Date[Month] -月份标签，然后它将这个筛选器与该Date[Year] 的筛选器交集，如图所示：
将覆盖应用于任意形状的集合，可能会导致意外的结果，筛选器结果现在包含2007年的1月和12月，因为该年上的新筛选器从前一个筛选器中删除了[Year]列。在这样做时，它破坏了存储在筛选器中的关系，并将其替换为形状良好的筛选器，这其实不是我们在编写公式时所期望的。
作为一项规则，我们可以声明如下：当使用形状良好的筛选器时，交集和覆盖工作都是以直观的方式进行的。当使用任意形状的筛选器时，Intersecect通过与其结果相交来保持其形状，而覆盖则可能破坏筛选器产生的结果，该结果丢失了存储在原始筛选器中的一些关系。
注意：CALCULATE ( CALCULATE (,,,, Date[Year] = 2007)， ,,,)，与之前我们多数情况下提到的： CALCULATE (,,,, Date[Year] ， ,,,)形式是不同的。前者是一个被定义的值列表筛选（这里是Date[Year] = 2007）下的结果表，其引用的来源列表（Date[Year] ）可认为不再存在（或已被删除，确切的说是删除了与其他列表之间的关系），B筛选(Date[Year] = 2006)其实已失去引用的原列表，筛选不起作用（或被删除，即B- Cleaned），该B- Cleaned与A（这里是Date[Year] = 2007）相交，自然只存在A筛选。
由于Date表的其他列表还是列表状态（包括[Month] ） Date[Month] = "December" ，Date[Month] = "January"这些引用[Month] 列的列值的值列表筛选并不受影响。
后者公式中的Date[Year] 引用的是全集的列表筛选，所有列表并没有收到破坏。
当然，在实际运用上，很多时候并不需要考虑这么多，但作为理解，你必须要清楚的知道定义值列表与引用列表参与筛选计算，它们之间的这种微妙区别。
直到第40式 的完成，我们终于初见成效，对于DAX的操作列表计算已经胸有成竹，知道DAX的语言交流是如此的简单直接：引用列表与定义值列表。剩下的就是实验与升华。
第41式 CALCULATE 的 ALL函数（进化）
到目前为止，我们在编写的大多数代码案例中都广泛使用了ALL函数。但是，仍然有一些需要了解的地方。实际上，我们知道，根据参数的不同：ALL()列表数据集都返回一个或多个列(含整个表)的所有值。
虽然，将ALL用作表函数时，这是正确的，但它的行为(在CALCULATE（）中用作筛选器参数)时则是不同的，而且是如此的不同，以至于我们认为它的功能更应该是另一个名字，即REMOVEFILTER（移除筛选器）。让我们详细说明这一点。
当编写如下表达式时，我们可能相信这样一个事实：ALL ( Date[Year] )由于ALL函数功能：它返回Date[Year] 的所有值，而且这个新的筛选器：将取代上一个筛选器Date[Year] = 2007，并生成了一个筛选器筛选，其中包含了该[Year]--年的所有可能的值（Date[Year] 列的所有列值）：
CALCULATE (
CALCULATE (,,,ALL ( Date[Year] ))，Date[Year] = 2007)
不幸的是，这并不是ALL()的工作方式。当ALL()用作CALCULATE中的筛选参数时，ALL所做的并不是返回列。相反，它从筛选中删除列。这就是我们建议使用REMOVEFILTER的原因。
这种差别是微妙的，但正如在本节中所了解的，这是一个重要的区别。如果所有这些Date[Year]--年都返回了，你会期望得到下图所示的行为：
图示可能希望ALL返回一个表的所有值，但情况并非如此。实际上，当ALL是CALCULATE的FILTER参数中的top-level--第一参数时，它会从筛选器筛选中移除其定义的列，这里是删除Date[Year]列，因此，产生的行为如图：
筛选器从其工作的列中移除，从而产生一个空筛选器。乍一看，这两种操作似乎没有区别。但实际运用中，这种行为是非常不同的，并更多地会使用到它。
显然，我们需要创建一些较复杂的筛选器。正如已经了解到的，当在筛选器中转换表时，筛选器包含表的扩展版本。因此，我们知道以下度量计算销售表中引用的'Date' -日期数：
[NumberOfSales] ：=
CALCULATE (COUNTROWS ( 'Date' ), Sales)
事实上，这里Sales表的扩展版本包含 'Date'--日期表的所有列(因为存在列表关系)。使用整个Sales表作为筛选器，可以有效地筛选日期表，结果是从Sales扩展表中引用日期表的总日期数。有时候，可能会期望同一度量的另一种表述会以同样的方式执行同样的计算：
[NumberOfSalesWithAll] ：=
CALCULATE (
COUNTROWS ( 'Date' )，ALL ( Sales ))
这是我们提到多几次的两个公式的对比：它们唯一的区别是，这一次在CALCULATE()的筛选器参数中使用了ALL ( Sales )。如果认为ALL()返回所有的销售行，那么，这两种方法的行为将是相同的。
然而，情况并非如此。应该把ALL()都看成是执行REMOVEFILTERS。在COUNTROWS计算之前，ALL()内容都会从当前筛选器筛选中移除所有筛选器，因为当前不包含任何筛选器，所以，也就是说，它并不修改筛选器筛选。实际上，NumberOfSalesWithAll的结果不是引用Date-日期的数量。相反，它返回Date-日期表中的日期总数(这时候并不是扩展表里的Date-时期表)。
值得注意的是，只有将REMOVEFILTERS用作CALCULATE的筛选参数中的最大粒度参数时，ALL()的行为才会表现为REMOVEFILTERS--删除筛选器。当使用它作为一个常规表函数时，完全可以完成它本应该做的事情：返回一个表。
因此，如果以下度量值以列方式编写，就会得到预期的结果。
[NumberOfSalesWithAllAndFilter] : =
CALCULATE (COUNTROWS ( 'Date' )，FILTER ( ALL ( Sales ), TRUE () ))
实际上，这一次我们使用的是FILTER筛选器操作中的ALL内容，而不是筛选器参数中的top- level。因此，FILTER遍历整个事实表并返回其所有行。然后，该完整的事实表（包含所有行）是CALCULATE的筛选器参数，所以它被扩展并用作筛选器，这样就可以达到筛选日期表的目的。我们将常见的四种公式形式放在一起，请你分析一下。
1) [NumberOfSales] ： = CALCULATE (COUNTROWS ( 'Date' ), Sales)
2) [NumberOfSalesWithAll] ：= CALCULATE (COUNTROWS ( 'Date' ), ALL ( Sales ))
3) [NumberOfDatesWithAll] ：= CALCULATE (ALL( 'Date' [date]), Sales )
4) [NumberOfSalesWithAllAndFilter] : =
CALCULATE (COUNTROWS ( 'Date' )，FILTER ( ALL ( Sales ), TRUE () ))
未完待续
网友评论