DAX专题9:FILTER函数-读书笔记(17)

FILTER()是DAX中最强大的表函数之一，在前两章中我们介绍过：表函数的主要目的是可以让我们在度量值中创建和使用虚拟表。这不是DAX中表函数的唯一用途，但这种用法是最常见的。这些虚拟表非常有用，因为我们可以使用FILTER表带来的所有好处，而不需要在PBI模型中把它变成实体表。当FILTER()和CALCULATE函数双剑合璧时，它允许你以任何你想要的方式改变公式中的筛选(也就是筛选上下文)。

语法：

= FILTER(Table, [Filter])

FILTER第一参数的表，可以是模型中的任何表，或者来自于表函数创建的虚拟表。

FILTER第二参数，是任何返回结果为 True/False 的表达式。

开始在CALCULATE()中使用FILTER()之前，我们有必要看一下FILTER()如何工作的两个概念。

 FILTER函数返回的表来自模型的数据源表，它可以包括0行，或更多行数据。FILTER函数的作用就是按第二参数的条件从第一参数表中筛选出符合条件的表。从技术上来讲，FILTER函数是一个迭代函数，它逐行扫描第一参数表，然后逐行看本行数据是否符合筛选条件(FILTER第二参数),如果符合就留下这一行，不符合就扔掉，直到把第一参数的表所有行都过滤一遍。与其把FILTER函数看成是迭代器函数，不如把它的工作方式当成是表中的计算列。

一、FILTER 函数有点像计算列 FILTER IS KIND OF LIKE A CALCULATED COLUMN

FILTER函数的行为跟计算列有点类似。为了正好的解释这一点，我们通过一个计算列的演示来证明一下。

我们先转到数据视图，右键单击Customers 表，选择新建列，在公式栏输入Test = Customers[YearlyIncome]，可以看到新列的值是跟[YearlyIncome]的值是一一对应的拿过来的

然后我们把公式改一下，Test = Customers[YearlyIncome]>=80000,新建列的值根据判断的结果，变成了TRUE或FALSE。

现在我们想一下TRUE或FALSE是怎么来的，Customers[YearlyIncome]每一行的值都与80000进行比较，把比较的结果添加到新建列的每一行中。我们在新建列中点击筛选按钮，选择TRUE, Customers[YearlyIncome]>=80000的所有行就被筛选出来了，一共是4382位客户年收至少是80000. 新建列的生成过程是对表中的每一行进行计算，然后把结果放在与新列对应的行单元格中。

二、要多用FILTER函数，而不是使用计算列

实际上我们可以把FILTER的运算过程当成上节例子中的新建列，FILTER运算开始时我们想像成：FILTER先把要筛选的表拷贝出一份来当成一个虚拟表(FILTER的第一参数表)，然后用Test = Customers[YearlyIncome]>=8000在这个虚拟表中添加一个列返回TRUE/FALSE(FILTER的第二参数)，新建列完成以后FILTER在新建列中点开筛选按钮，选择TRUE，筛选出新建列中结果为TRUE的表的所有行。这个过程就是FILTER背后的操作流程。

FILTER函数的最大优点就是：它不需要在实体表中真的添加一个新列，而是在内存中完成新建列、筛选列的操作，一旦FILTER计算完成，虚拟的新列就完成任务在内存中释放了。

现在我们知道了FILTER的运算过程，我来给大家演示一下现实在FILTER的等效操作。再说明一点：FILTER是表函数，我们不能把它直接放到度量值里面，就像不能把一列放在度量值里面一样。度量值必须返回一个值，不能返回一个表或表的列。为了便于理解这一点，下一节向大家介绍另一个思路，并给大家介绍新建表功能的使用。

三、新建表又来了(实际上我们是用可见表来 解释Filter 返回的虚拟表)

大家回想一下上一节中第一段文字的描述，在脑海中脑补一下FILTER的工作过程。这次我们把刚才解释FILTER在内存中运行的过程实实在在的展示出来。点击新建表按钮，在公式栏输入：

Customers > 80000 Table = 

  FILTER(ALL(Customers[CustomerKey],Customers[Name],Customers[YearlyIncome]),

    Customers[YearlyIncome] >= 80000

    )

可以看到，FILTER函数返回的表的行数跟上一节示例中演示的结果是一样的。

大家要记住：我们创建这个表只是为了展示发生了什么，实际使用中我们不需要创建这个表，只要把它作为度量中的虚拟表(筛选参数表)使用就行了。

四、在Calculate里面使用Filter函数

这一节我们看看如何中度量值中使用FILTER函数。转到报表视图，新建一个矩阵，把Customers[Occupation]放到行上，把度量值 [Total Number of Customers]放到值上，矩阵展示出每个occupation type的客户数量。

下面我们用FILTER写一个度量值，使用上节的FILTER公式 = FILTER(Customers, Customers[YearlyIncome] >= 80000)，刚才提到过：表不能放在度量值中，所以要给这个公式套上个聚合函数，我们使用COUNTROWS,

Total Customers with Income of $80,000 or above Measure =

      COUNTROWS(FILTER(Customers, Customers[YearlyIncome] >= 80000))

五、Filter 不改变初始筛选上下文 

上一节的例子中，我们看到不是所有的职业中都有年收入大于80000的客户，clerical 和 manual 返回的就是空值。

Total Customers with Income of $80,000 or above Measure =

    COUNTROWS(FILTER(Customers, Customers[YearlyIncome] >= 80000)    )

度量值中FILTER的第一参数是Customers表，在可视化报表中(如矩阵)这个表就不是数据视图中的整个Customers表了，它依赖矩阵的初始上下文。我们之前讲过矩阵中的每个值单元格是如何计算的，上一节的度量值放到矩阵中时，对于矩阵中的每个值单元格，传递给FILTER第一参数的表不是整个Customers，是每个值单元格对应的矩阵的行字段筛选后的表(这就是初始筛选)([Occupation]="Clerical"--下图红框筛选Customers)，筛选以后再按FILTER第二参数筛选一次（Customers[YearlyIncome] >= 80000再筛选一次Customers），然后COUNTROWS再对两次筛选后的表计算行数。 每个值单元格都是这样计算的。这就解释了为什么有的职业年收入大于80000的客户为空值。

原文是这样描述的：

The first row of the matrix above is evaluated as follows:

1. The matrix visual places a filter on Customers[Occupation] = "Clerical". As a result, the Customers table is now filtered to the 2,928 rows that are clerical customers.

2. The first parameter of FILTER() therefore receives a filtered copy of the Customers table, filtered for Customers[Occupation] = "Clerical".

3. Before FILTER() even starts to do its stuff, the Customers table has already been filtered by the visual. Instead of the first parameter (the table) having 18,484 rows, it actually has only 2,928 rows because it is already filtered for clerical customers.

六、要点

🔶 These are the key points to take away from the examples before:

• FILTER() returns a table. It is a virtual table, and hence you can't see it.

• You can think of FILTER() as operating like a calculated column inside a table—but better . It is better because it can do everything a calculate column can do (in this case) but doesn't need to materialise a new column into the table. In Power BI, fewer columns is always better • You can materialise a FILTER() function by using the New Table option if you want to see how it works.

• The virtual table returned by FILTER() has a virtual relationship to its source; this is called lineage. The virtual table and virtual relationship behave in the same way that physical tables and relationships behave.

• The virtual table returned by FILTER() can be used inside a measure without the need to materialise the table at all.

• Materialising can be a great technique to help you visualise what is going on in DAX. You should materialise tables in order to learn and comprehend, but you should not leave such tables in your model. Delete them when you are done using them.

• You can't use table functions (naked tables) on their own inside measures; they must be used inside other functions so that they return single values. In the example above, for example, I used COUNTROWS() to count the rows in the table.

•FILTER()返回一个表，它是一个虚拟表，因此无法看到它。

•可以将FILTER()看作是在表中计算列的操作，它可以完成计算列所能做的所有事情而不需要将新列物化到实体表中。

•在Power BI中，表的列越少越好。

•如果你想看看它是如何工作的，你可以使用新建表来把FILTER()函数返回的表具象化。

•FILTER()返回的虚拟表和FILTER原表(第一参数表)实际上存在着一种虚拟关系，这就是所谓的数据沿袭。虚拟表和虚拟关系能做的与模型中实体表和模型的关系能做的是一样的(像筛选、筛选传递等)。

•FILTER()返回的虚拟表可以用在度量中，不需要转换成实体表。

•将虚拟表具象化是一种很好的方法，这样做可以帮你把DAX中发生的事情复现化。在学习和理解DAX概念和计算过程时，应该将表实体化，实体化的表在测试完数据或度量值以后就删除了吧，模型中其实不需要保留这样的表。

•不能在度量值中单纯放一个表函数(就是我们常说的裸表，度量值中放表的列也是不可以的)，它们必须和其它函数配合使用，以返回单个值(标量值)。例如，在上面的示例中，我使用COUNTROWS()来计算表中的行数。

🔶 A Caution Related to Using FILTER()

Generally, it is fine to use FILTER() on lookup tables, but it’s somewhat riskier to use it on data tables, particularly if they are very large (with hundreds of millions of rows). Where to use FILTER() depends on your data, on the quality of your DAX formulas inside FILTER(), and on what you need to achieve. You will increase your depth of understanding with practice and experience. When you have a choice, aim to use FILTER() on the smallest table possible to keep your formulas working efficiently.

一般来说，在维度表上使用FILTER()是可以的，但在数据表上使用它有点风险，特别是如果数据表非常大(假如有数亿行)。在哪里使用FILTER()取决于你的数据量、FILTER中DAX公式的代码质量以及你的目的。通过不断练习和经验积累，你会增加对DAX理解，当你有选择的余地时，尽量在尽可能小的表上使用FILTER()，好让公式更高效地工作。

七、 CALCULATE里面使用FILTER   FILTER  Inside CALCULATE()

本章开头说过，FILTER函数通常用作CALCULATE函数中的表筛选参数。FILTER()是一个表函数，它对表中的每一行进行操作来确定保留哪些行。因此FILTER()允许对表进行非常细粒度的计算，并且是一个非常强大的工具，可以按照我们想要的任何方式和详细级别更改可视化对象的筛选上下文，而在CALCULATE()中使用简单筛选(布尔表达式筛选)并不总是可以做到这一点。

Total Customers with Income of $80,000 or above 2 =

    CALCULATE(COUNTROWS(Customers),Customers[YearlyIncome] >= 80000)

这个公式返回的结果和度量值

Total Customers with Income of $80,000 or above Measure =

    COUNTROWS(FILTER(Customers, Customers[YearlyIncome] >= 80000))

返回的结果完全相同。第一个使用 CALCULATE写的度量值公式中使用了简单筛选(布尔表达式筛选)，简单筛选就是使用判断表达式进行的筛选，CALCULATE函数设计之初是接受这种写法的，实际上这是CALCULATE函数完全写法的一种简化(也可以称之为语法糖)，这样写只是为了书写简单度量值时更方便。这种写法的背后，隐藏着它的完整语法书写形式：

Total Customers with Income of $80,000 Under the Hood =

    CALCULATE(COUNTROWS(Customers),FILTER(ALL(Customers[YearlyIncome]),            Customers[YearlyIncome] >= 80000))

注意：FILTER()函数的第一个参数使用了ALL(Customers[YearlyIncome])，而不仅仅是Customers。下一章中会给大家举几个更详细的例子来说明为什么在这里使用ALL函数。CALCULATE使用简单条件筛选有一些限制，如果你想做一些复杂的事情的话，还是需要使用CALCULATE的完整语法形式。

八、示例：计算购买金额大于某值的客户数量 Example: Calculating Lifetime Customer Purchases

现在我们要计算购买金额大于3000元的全部客户数量，我们不能在CALCULATE函数中使用简单筛选，因为表中没有这样一个列，含有一个单一值，这个值又包括每个客户的所有购买金额。我们试着这样写一个

Customers with Sales Greater Than $3,000 Doesn't Work =

                   CALCULATE(COUNTROWS(Customers), [Total Sales] > 3000)

公式里面第二参数是[Total Sales] > 3000，左边是一个度量值，右面是一个数值，在DAX里面这样写是语法上不允许的，DAX无法运行这个公式。

报错信息 ：函数"PLACEHOLDER"已用于一个True/False布尔表达式，该表达式用作表筛选表达式。这是不允许的。

这种情况下，还是需要使用表筛选函数做为CALCULATE的第二参数

Customers with Sales Greater Than $5,000 = CALCULATE(COUNTROWS(Customers),

                           FILTER(Sales, [Total Sales] > 3000))

九、 对比一下Filter的简写语法和完全写法 Simple Filter Syntax vs. Table Filter Syntax

如果你的筛选条件只是想比较一个列和一个值，你就可以使用CALCULATE的简单筛选写法。如果你想保持简单的写法，CALCULATE能够很好的处理这种简单筛选。如果想处理复杂的情况，不单单是比较一个列和一个值的话，那只用CALCULATE函数就处理不成了(这里指的是不用第二参数)。这时CALCULATE需要它的强大帮手FILTER来帮忙。CALCULATE委托FILTER函数把符合条件的行筛选出来，FILTER把筛选出来的表再回传给CALCULATE，CALCULATE第一参数表达式计算出结果，这两个函数相互配合完成工作。

Customers with Sales Greater Than $5,000 = CALCULATE(COUNTROWS(Customers),

                           FILTER(Sales,  [Total Sales] > 3000))

本例中我们理解了FILTER的工作原理，它和稍早包含Customers[YearlyIncome] >= 80000的代码还是稍有不同的。不论是简单筛选还是表筛选，CALCULATE函数总是先运行筛选再计算。

我们再看看上面公式中FILTER(Sales, [Total Sales] > 3000)部分，第一小节中我们说过，FILTER函数运行过程跟新建列的过程类似，我们把度量值[Total Sales]写成新建的列的代码，

新建列的值是每种产品的总销量，从直观上就能看出来每种产品的总销量是相同的(事实表中有很多产品名称重复的记录，相同名称的产品的总销量是相同的，不同名称的产品销量是不同的，这也是度量值天生能被筛选的特质得到的)。如果你更深入地思考这个结果，你会发现实际上有很多事情发生。度量值[Total Sales] 公式代码是 样的  Total Sales = SUM(Sales[ExtendedAmount])，Total Sales把Sales表的产品销量列做了汇总求和，但新建列是在Customers(客户)表里的，不是在Sales表里，新建列公式却干得很好，至于为什么？大家可以自行研究一下。

十、重新审视一下上下文转换这个概念 Revisited Transition Context

上一节的示例中，我们把度量值放在新建列里，公式是 TEST NEW COLUMN = [Total Sales]，度量值[Total Sales] = SUM(Sales[ExtendedAmount])，

得到了想要的结果。如果我们在新建列里输入公式 TEST 3 SUM= SUM(Sales[ExtendedAmount])，得到的值全是一样的，没按产品区分，显然这是不对的，为什么会这样呢？

在概念6那一章时给大家讲过一个"看不见的CALCULATE引擎   The Hidden Implicit CALCULATE()",    其实我们把度量值放在新建列里时，隐藏在度量值公式里的CLACULATE就把行上下文转换成了筛选上下文，正是这个上下文转换，表的每一行变成了一个筛选器，这个筛选器顺着模型关系从Customers表传递给Sales表，Sales表筛选后，度量值计算时每个客户的销量汇总就按每个客户筛选区分了。但TEST 3 SUM= SUM(Sales[ExtendedAmount])只是行上下文，没有转换成筛选上下文，所以每一行的值都是数量的汇总。

下面我们把新建列里的度量值代码修改一下：TEST NEW COLUMN = [Total Sales] >=3000, 新建列的值变成了TRUE/FALSE，我们再点击新建列的筛选箭头，选择TRUE，结果如下：

我们这样做只是为了帮助大家理解和学习，并不是每次想计算时我们都要创建一个新列，这种做法也不是解决问题的最好办法，我们应该使用FILTER函数。FILTER函数工作时就可以在任何表中添加一个虚拟列，而不是在实体表中真的新建一个列。我们再把完整版公式写一下

Customers with Sales Greater Than $5,000 = CALCULATE(COUNTROWS(Customers),

 FILTER(Sales,    [Total Sales] > 3000) )

这个公式运行步骤如下：

1. FILTER()函数创建一个新的虚拟表，其中包含一个虚拟的True/False计算列。

2. FILTER()函数通过筛选新建列，只保留新列中值为True的行。

3.FILTER()返回筛选后的虚拟表，CALCULATE()用这个表筛选模型。

4. FILTER()创建的虚拟表格不可见，但可以通过使用刚刚的示例通过计算列或使用"新建表"按钮实现公式的FILTER()部分来模拟它的工作。

在CALCULATE中使用FILTER函数，虽然没有永久存储新表或新列来给数据模型添麻烦，但是可以很好的完成复杂的筛选。

十一、FILTER函数练习

1、求销售金额(大于0，小于10000元)

total sales less 10000 and morethan 0 = CALCULATE([TOTAL SALES],

FILTER(Products,[total sales] <10000 && [total sales] > 0))

/*   = CALCULATE([Total Sales], FILTER(Products, [Total Sales] <10000),        FILTER(Products, [Total Sales] >0))   */

2、求销售金额(大于0，小于10000元)的客户数量

total sales productsnumber = CALCULATE(COUNTROWS(Products),

    FILTER('Products',[TOTAL SALES] <10000 && [total sales] >0))

/* = CALCULATE(COUNTROWS(Products), FILTER(Products, [Total Sales] <10000),        FILTER(Products, [Total Sales] >0))*/

十二、进一步理解上下文转换

在概念6那一章和本章的前面小节我们介绍了上下文转换的概念，我们通过新建列演示了FILTER是如何进行上下文转换的。上下文转换是个比较难理解的概念，尤其比较难的脑补的地方是在像FILTER返回的虚拟表中转换上下文时，因为FILTER返回的表中发生的上下文转换是不可见的。之前我们写的度量值是这样的：

Customers with sales greater than $5,000 = 
CALCULATE(COUNTROWS(Customers), FILTER(Customers, [Total Sales] >= 5000))

公式里面的 [Total Sales] 公式： [Total Sales] =SUM(Sales[ExtendedAmount]),如果我们把度量值里面的[Total Sales]换成SUM(Sales[ExtendedAmount])，上面度量值公式变成这样

Customers with sales greater than $5,000 Version2 =    CALCULATE(COUNTROWS(Customers), FILTER(Customers,SUM(Sales[ExtendedAmount]) >= 5000))

total customers that have purchased = CALCULATE([TotalCustmersNumber],

FILTER(Customers,[TOTAL SALES]>0))

[Total Sales] = SUM(Sales[ExtendedAmount])

我们把这几个度量值放到矩阵里面

Ver2度量值返回的是一个不能筛选的值，因为FILTER的第二参数 SUM(Sales[ExtendedAmount]是一个行上下文，不能被筛选。

我们写度量值时[Total Sales] = SUM(Sales[ExtendedAmount])，PBI在后台其实给公式套上了一个CALCULATE函数，

[Total Sales] = CALCULATE(SUM(Sales[ExtendedAmount]))

所以，如果我们把Ver2改一下：

Customers with sales greater than $5,000 Version3 =     CALCULATE(COUNTROWS(Customers), FILTER(Customers,CALCULATE(SUM(Sales[ExtendedAmount]) >= 5000)))

下面我们把矩阵的行字段换成国家，可以看到度量值不会管谁在矩阵行上筛选，它都能正常工作

再给大家啰嗦一下：SUM(Sales[ExtendedAmount])外面没有套上CALCULATE时，上下文转换是不会自己完成的。SUM(Sales[ExtendedAmount])外面有CALCULATE时，或者隐式的存在一个CALCULATE时，上下文转换都能完成，就能得到正确的结果。所以有些情况下需要我们手动添加一个CALCULATE函数才能让公式正常运行。在第11章我们也说过，行上下文不能自动转换成筛选上下文。之前在新建列时我们谈到的上下文转换的情况跟FILTER上下文转换的情况是一样的。刚刚的示例VER2度量值中FILTER函数没有显式的CALCULATE,也没有隐式的CALCULATE,所以Customers表不能把筛选传递给Sales表。VER3公式中我们给FILTER里面加了一个CALCULATE后，Customers表就可以传递筛选了，结果就对了。

十三、重提虚拟表的数据沿袭这个概念 Virtual Table Lineage Revisited

在第13章我们介绍过虚拟表的数据沿袭问题，把虚拟表想像成一个实体表放在模型中，重新看这个概念也是为了巩固一下知识点。

Customers with Sales Greater Than $5,000 =    CALCULATE(COUNTROWS(Customers),

FILTER(Customers, [Total Sales] >= 5000))

我们可以把上面这个公式中FILTER函数返回一个虚拟表想像成是模型中的一个表。表函数创建的虚拟表和模型中的其它表也具有关系，我们把这种关系的延伸叫“数据沿袭”。我更喜欢用视觉化的方式把表函数创建的虚拟表在模型中的样子放在模型视图中。

FILTER函数筛选出来的表是一个数据子集(产生这个虚拟表的原表的子表)，虚拟表和产生虚拟表的原表保持着关系的延续(数据沿袭)。当这个表在CALCULATE函数中被使用时，CALCULATE会把FILTER筛选出的虚拟表做为筛选条件顺着关系箭头方向传递给模型的其它表。上面这个图是想像出来的，只是为了具象化虚拟表和模型的关系，虚拟表的所有行为遵循上图中关系的约束。

NOTE:通过新建表菜单创建的表(实体表)跟模型并没建立关系，如果想让这种表筛选模型，那就需要手工建立 和其它表的关系。