DAX 第三篇：过滤器函数

过滤器函数允许你操纵筛选上下文以创建动态的计算，是DAX中最复杂和最强大的一类函数，本文记录我对一些常见的过滤器函数的总结。

一，筛选上下文的构成

DAX中的筛选上下文由三部分构成：交叉过滤构成的过滤，查询上下文中每行的列值构成的过滤，外部切片器构成的显式过滤。

1，交叉过滤器

在数据模型中创建关系，指定交叉过滤器

2，查询上下文

 在当前查询的上下文中，每行的列值也会作为过滤器，例如，ProductCategoryName和Color是当前查询上下文的行过滤器：

3，外部切片器

在报表中设置的切片器，是在查询上下文外部的设置的过滤器，例如，上图中的Color切片器就是外部切片器。

二，清除过滤器

ALL系列的函数能够从上下文中清除已经应用的过滤器。

1，ALL

ALL 函数用于清除表上的所有过滤器，返回表中的所有行，或者一列的所有值，适用于对表中的所有行进行汇总计算。注意，此函数只能用于基础表(base table)，不能用于表表达式或列表达式。

ALL( [<table> | <column>[, <column>[, <column>[,…]]]] )  

2，ALLCROSSFILTERED

ALLCROSSFILTERED函数用于清除应用到表上的所有过滤器，该函数不返回任何值，仅仅用于清除表上的过滤器。

ALLCROSSFILTERED(<table>)

例如，计算FactInternetSales表上所有OrderQuantity的值：

= CALCULATE(SUM(FactInternetSales[OrderQuantity]), ALLCROSSFILTERED(FactInternetSales))

3，ALLEXCEPT

ALLEXCEPT函数用于移除其他字段的所有过滤器，而保留参数指定的字段上的过滤器，

ALLEXCEPT(<table>,<column>[,<column>[,…]]) 

返回一个表，除了参数指定的字段上的过滤器之外，其他字段的过滤器都被移除，例如，保留 DateTime[CalendarYear] 字段上的过滤器，表DateTime中其他字段的过滤器都被移除：

= CALCULATE(SUM(ResellerSales_USD[SalesAmount_USD]), ALLEXCEPT(DateTime, DateTime[CalendarYear]))

4，ALLSELECTED

ALLSELECTED 的作用是从当前查询的上下文中移除的行和列的过滤(即忽略行和列的过滤)，而保留当前查询除行和列之外的上下文过滤器或显式定义的过滤器(例如切片器等)：

ALLSELECTED([<table> | <column>[, <column>[, <column>[,…]]]] )  

参数table和column是可选的，如果有多个column参数，那么这些column必须来自同一个table。返回值是不包括行和列过滤器的查询上下文。

例如，计算选定的颜色和分类的产品销售数量的占比。在报表中设置切片器，共选择了4种颜色，这是报表显式定义的过滤器。

• 当前查询的上下文过滤器是由ProductCategory和Color，以及外部的切片器构成的；
• 分母的计算逻辑是移除当前查询的上下文过滤器，也就是不管产品ProductCategory和Color，计算4种Color(外部切片器决定)对应的产品销售的总量，四种颜色对应的SalesAmount总量是12944190.35。
• 分子的计算逻辑是在当前查询的上下文中，计算特定的ProductCategory和Color的产品销售的数量。例如，颜色为Red的Bikes的销售总量是7646302.82，除以SalesAmount总量12944190.35，约等于0.59。

SalesRateBySelectedColors = DIVIDE(CALCULATE(SUM( FactSales[SalesAmount]))*1.0, CALCULATE(SUM(FactSales[SalesAmount]),ALLSELECTED()))

5，REMOVEFILTERS

移除过滤器，不会返回表

REMOVEFILTERS([<table> | <column>[, <column>[, <column>[,…]]]])

三，添加过滤器

在DAX中对表进行过滤，用于在表达式中增加过滤器：

FILTER(<table>,<filter>) 

参数是布尔表达式，用于逐行执行，对表进行过滤，返回结果为True的行，而把结果位False的行移除，例如：

FILTER(FactSales, [Amount] > 0 or [Region] = "France")

四，直接过滤和交叉过滤

数据模型中的过滤分为直接过滤和交叉过滤：

直接过滤：当过滤直接作用到表中的列col上时，列col是被直接过滤的。直接过滤是由外部的切片器和查询上下文中的行和列过滤器确定的，不受交叉过滤器的影响。

注意：如果外部切片器没有勾选任何选项时，默认显示基础表中所有的唯一值。

交叉过滤：当过滤作用于同一个表的另一列B上，或作用于相关表上时，列A是被交叉过滤的，交叉过滤收到所有筛选上下文的影响。

direct_filtered_colors = COUNTROWS((FILTERS(DimProduct[Color])))
cross_filtered_distinct_colors = COUNTROWS((DISTINCT(DimProduct[Color])))
cross_filtered_values_colors = COUNTROWS((VALUES(DimProduct[Color])))

结果分析：

direct_filtered_colors：是直接过滤，不受交叉过滤上下文的影响，直接显式基础表中Color的唯一值的数量，

cross_filtered_values_colors 和 cross_filtered_distinct_colors ：是交叉过滤，受到交叉过滤上下文的影响，只统计被过滤之后的Color的数量。

1，判断是直接过滤还是交叉过滤

直接过滤和交叉过滤可以通过函数来识别：

ISCROSSFILTERED(<columnName>) 
ISFILTERED(<columnName>) 

2，返回过滤的唯一值

移除重复，只保留唯一值，当返回直接过滤的唯一值时，使用FILTERS()函数：

FILTERS(<columnName>) 

当返回交叉过滤的唯一值时，使用DISTINCT 和 VALUES函数，这两个函数都受到过滤上下文的作用。唯一的区别是：

当原始表中不存在BLANK时，DISTINCT 只返回原始表中的值，而VALUES可能会返回BLANK，该数据是被添加到查询中用于表示不相关的行。

DISTINCT 和 VALUES函数的定义是：

DISTINCT(<table | column>) 
VALUES(<Table | Column>) 

3，检查唯一

当直接过滤的唯一值只有一个时，返回True；否则返回False。

HASONEFILTER(<columnName>)

该函数等价于：

COUNTROWS(FILTERS(<columnName>)) = 1

当列上下文中只有一个唯一值时，返回True；否则，返回False

HASONEVALUE(<columnName>) 

该函数等价于：

COUNTROWS(VALUES(<columnName>)) = 1

4，SELECTEDVALUE

 当column的上下文被过滤为只有一个唯一值时，返回该唯一值，否则返回alternateResult。

SELECTEDVALUE(<column>[, <alternateResult>])  

该函数等价于：

IF(HASONEVALUE(<column>), VALUES(<column>), <alternateResult>)

五，关联值

关联需要当前表和关联表之间存在关系。跟当前行相关的唯一值，该函数不受任何过滤器的影响：

RELATED(<column>) 

RELATED函数需要当前表和关联表之间存在关系，在函数中指定包含你需要数据的列，该函数遵循现有的多对一关系，以从相关表的指定列中获取一个值，也就是说，RELATED函数从多方关联到1方的一个值，参数column是1方表中的列。当RELATED函数执行查找时，它会检查指定表中的所有值，而不管可能已应用的任何过滤器。

注意：RELATED函数需要行上下文，因此，只能用于计算列表达式中(其当前行上下文是明确的) 和表扫描函数中。表扫描函数(如SUMX)获取当前行的值，然后扫描关联表以查找指定列的关联值。 

例如，从下图中可以看到，FactSales和DimProduct之间存在 “*对1” 的关系，并且FactSales是多方，DimProduct是1方。可以在RELATED()函数中指定DimProduct表中的列，以从DimProduct表获得跟FactSales中某一行相关联的值。

在FactSales表中创建计算列，获取跟当前行相关的产品颜色的值：

Related_color = RELATED( DimProduct[Color]) 

参考文档：

Filter functions

allselected()函数