从数据到信息
从信息到洞察

理解 FILTER

初识迭代函数

广义的迭代是对反馈过程的重复,其目的通常是为了接近并到达所需的目标或结果。每一次对过程的重复被称为一次“迭代”,而每一次迭代得到的结果会被用来作为下一次迭代的初始值。

在 DAX 中,迭代的含义有所不同,迭代函数遍历整个表,为表的每一行执行相同的 DAX 表达式,然后根据不同的函数执行不同的后续操作。DAX 的迭代函数数量很多,主要有两类

  • 以 X 结尾的所有聚合函数,比如 SUMX,AVERAGEX 等,它们迭代后对所有值进行相应的聚合运算
  • FILTER、ADDCOLUMNS、SELECTCOLUMNS、RANKX 等其他函数

它们都可以创建行上下文。本章我们介绍 FILTER 和以 X 结尾的聚合函数

理解 FILTER

FILTER 函数的作用很简单:它获取一个表并返回一个与原始表具有相同列的表,逐行应用筛选条件,最后返回满足筛选条件的行。

FILTER 语法

FILTER ( <table>, <FilterExpression> )

FILTER 迭代整张表<table>,为每一行计值布尔表达式<FilterExpression>,当计值结果为 TRUE,FILTER 返回当前行;否则,就跳过当前行。

从逻辑角度来看,FILTER 为表中的每个行计值条件表达式。但是,DAX 的内部优化逻辑可能会将这些计算的数量减少到条件表达式中包含的列的唯一值的数量。条件表达式的实际计算次数对应于 FILTER 操作的“粒度”(Granularity)。这个粒度决定了 FILTER 的性能,它是 DAX 优化的重要因素。

例如,下面的查询筛选出产品表中的 Fabrikam 品牌

EVALUATE
FILTER (
    Product,
    Product[Brand] = "Fabrikam"
)

只筛选 Fabrikam 品牌的产品

FILTER 嵌套

可以将对 FILTER 的调用嵌套在另一个 FILTER 函数中,因为任何返回表的表达式都可以用作 FILTER 的参数。最内层的 FILTER 首先执行。通常,嵌套两个筛选器与用 AND 函数包含逻辑条件的结果相同。换言之,以下查询产生相同的结果:

FILTER ( <table>, AND ( <condition1>, < condition2> ) )

FILTER ( FILTER ( <table>, < condition1> ), < condition2> )

但是,如果表有许多行,并且两个条件具有不同的复杂性,则可能会观察到不同的性能。例如,考虑下面的查询,它返回的单价是单位成本的三倍以上的 Fabrikam 产品,如下图所示。

EVALUATE
FILTER (
    Product,
    AND (
        Product[Brand] = "Fabrikam",
        Product[Unit Price] > Product[Unit Cost] * 3
    )
)

查询筛选出了单价是单位成本的三倍以上的 Fabrikam 产品

此类查询会将这两个条件应用于产品表的所有行。如果两个条件中有一个更快、更有约束性,则可以在内层的 FILTER 函数里首先应用它。例如,以下查询将对单位价格和单位成本的筛选应用于最内层的 FILTER 函数,然后按品牌筛选满足价格条件的产品。

EVALUATE
FILTER (
    FILTER (
        Product,
        Product[Unit Price] > Product[Unit Cost] * 3
    ),
    Product[Brand] = "Fabrikam"
)

如果反转条件,那么也会反转它们的执行顺序。以下查询仅将价格筛选应用于 Fabrikam 品牌的产品:

EVALUATE
FILTER (
    FILTER (
        Product,
        Product[Brand] = "Fabrikam"
    ),
    Product[Unit Price] > Product[Unit Cost] * 3
)

当你优化 DAX 表达式时,这些知识会很有用。你可以选择执行顺序,首先应用最具约束性的筛选器。然而,请在彻底清楚计值上下文之后再开始优化 DAX 语言。你将在 DAX 优化章节中找到关于查询优化的更完整的介绍。这些示例的目的是让你了解表函数在嵌套调用时的执行顺序。

随着 DAX 版本的更新,引擎的自动优化能力也在不断增强,在普通场景中,以上不同写法的实际性能差异已经微乎其微。但这种优化思路仍然有其参考价值,因为在更加复杂的模型和公式中,你无法完全依赖于引擎的自动优化。

关于执行顺序

通常,嵌套函数的执行顺序是从最内层函数到最外层函数。稍后你将看到,CALCULATECALCULATETABLE 可能是此行为的一个例外,因为用于计算它们的参数有特定顺序。考虑到你可能在某些相似情况下使用 FILTER 和 CALCULATETABLE,所以在嵌套调用时要注意这个区别。

测试题

你已经了解在 FILTER 中执行多个条件判断时的最优策略,让我们来回顾一下需要同时满足多条件(AND 运算)时,FILTER 所有可能的写法。以两个条件为例,使用 Contoso 样例数据库文件,定义以下三个公式:

CALCULATE (
    COUNTROWS ( Sales ),
    FILTER (
        ALL ( Sales ),
        Sales[Order Date] <= DATE ( 2007, 1, 1 ) 
            && Sales[Delivery Date] >= DATE ( 2006, 1, 1 )
    )
)
CALCULATE (
     COUNTROWS ( Sales ),
     FILTER (
         FILTER ( ALL ( Sales ), Sales[Order Date] <= DATE ( 2007, 1, 1 ) ),
         Sales[Delivery Date] >= DATE ( 2006, 1, 1 )
     )
 )
CALCULATE (
      COUNTROWS ( Sales ),
      FILTER ( ALL ( Sales ),[Order Date] <= DATE ( 2007, 1, 1 ) ),
      FILTER ( ALL ( Sales ),[Delivery Date] >= DATE ( 2006, 1, 1 ) )
)

它们的用时排序是怎么样的?

公众号二维码加载失败时的替代文字
此处内容已经被作者无情的隐藏,请输入验证码查看内容
验证码:
请关注“PowerBI极客”公众号,回复关键字“FILTER”,获取验证码。 【注】手机扫描二维码快速关注“PowerBI极客”官方公众号。

 

参考阅读:CALCULATETABLE vs FILTER

39
说点什么

1000
 
鼓掌微笑开心憧憬爱你色并不觉得吃瓜doge二哈喵喵思考笑哭捂脸悲伤大哭抓狂汗偷笑打脸捂眼黑线问号晕拜拜闭嘴衰咒骂ok作揖
9 评论数
32 被回复的评论
10 订阅评论的人数
 
查看最近回复
查看最热评论
  订阅本文评论  
最新 最旧 得票最多
提醒
成员
131****8603

老师您好,我算是新手吧,在用公司数据摸索过程中遇到个问题,想用SUMMARIZECOLUMNS新建一个表,这个新表里面有写一个用SUM计算的新列,但计算表达式使用的原列有一些数字按其他列分组后聚合出的值为0,我现在想要达成的效果是SUMMARIZECOLUMNS生成的新表中,那个SUM计算出的新列里面是已经把0筛选掉的,但不知道如何才能达成这样的效果,百度也搜不到类似聚合后筛选的例子,求指教,谢谢!

成员
xianwensu

老师您好,我还是重新开一个问题,仔细问一下我考虑下面有个门店数产生的疑问。
我自己准备了一个例子,然后做了一些测试。请看附图,附图左边是表格内容,右边是我测试的一些数据结果展示。
度量值的建立没有实际意义,只是为了想验证一些计值机制。
度量值如下:
1. 销售金额 = SUM(‘table'[销售额])
2. test1 = SUMX(ALL(‘table’),AVERAGE(‘table'[销售额]))
3. test2 = CALCULATE(AVERAGE(‘table'[销售额]),ALL(‘table’))
4. test3 = SUMX(ALL(‘table’),’table'[销售额])
5. test4 = COUNTROWS(FILTER(ALL(‘table’),sum(‘table'[销售额])>350))
6. test5 = COUNTROWS(FILTER(ALL(‘table’),CALCULATE(sum(‘table'[销售额])>350)))

根据测试的结果,我理解的每个度量值计值是这样的:
1. test1: SUMX 对参数一(all表)每一行的值求和,这个值是 AVERAGE(作为最内层的表达式,它还是要考虑最外层上下文?) 对外部上下文的平均值。所以李四test1=average(李四销售额)*11(表总共十一行,每行计算结果都一样)
2. test2: Calulate会用内部表覆盖外部上下文,所以 这里的ALL(table)忽略了外部所有筛选器,所以返回的都是1996/11.
3. test3: SUMX 对参数一(all表)每一行的值求和,这个值是当前行的值,参数二不是表达式,所以它以SUMX内部给它的环境为主?all代表忽略外部上下文,返回全表,所以公式的计值结果始终是多有行的求和。

4. test4: Filter对参数一的每一行判断是不是符合参数二,符合的返回,参数二sum(作为最内层的表达式,它还是要考虑最外层上下文?)看外部上下文筛选的子集求和是不是大于350。是true的,对参数一的每一行返回,所以李四test4=sum(李四销售额)<350, 是false, 对参数一的每一行判断都是false, 所以返回0行,度量值结果是空。

好像字数限制,老师我评论中再继续。

table1.PNG
成员
xianwensu

5. test5: Filter对参数一的每一行判断是不是符合参数二,符合的返回,参数二因为嵌套calculate, 转为筛选上下文,对参数一的每一行判断参数二,参数二转为判断当前行是不是大于350. 结果是只有一行符合,所以Filter的表返回1.
疑问1: 为什么像test1和test4, 出现ALL表达式,首先最内层是根据最外层上下文计算结果呢?ALL对它不起作用,是因为在它外部吗?
疑问2:为什么test5 中和 楼下例子中的门店1感觉不一样,test5明显没有受到外部筛选器的影响,始终返回1.
实在令人费解

成员
yy813450880

高老师,我又来了 捂脸
1.FILTER ( , AND ( , ) )
2.FILTER ( FILTER ( , ), )
第2条我能理解是记住是and关系。但是我还是想问:filter我记得是创建行上下文,那第2个里面有两个filter为啥不是创建了两次行上下文 捂脸

成员
与你有关

你好 想试一下课后习题,但我发现我在前一章节下载的 “Contoso Sales Sample for Power BI Desktop”的sales表格里没有 record date和 delivery date,能麻烦您再分享下新的链接吗?

成员
mepub

老师您好,我这有一个sku 记录表,想要查看每一个区域中sku型号数量超过1000的门店有多少个,因此我写了三个度量值: 1 单品种类数=DISTINCTCOUNT(‘sku record'[skuid]) 2 超过1000个单品的门店数1=COUNTROWS( FI… 阅读更多 »

1590914556.jpg
成员
悟空

您好,想问您一个问题?我想求历史三个月的总和,并自动排除6月11月,您看图片,是否还有优化的空间?谢谢
=var a=month([Month])=7||month([Month])=8||month([Month])=9||month([Month])=12||month([Month])=1||month([Month])=2
var x=if(a,-4,-3)
var y= calculate(sum(‘表4′[Number]),filter(‘表4’,’表4′[Month]=edate(earlier(‘表4′[Month]),x)))
var z=if(a,CALCULATE(max(‘表4′[Number]),filter(‘表4’,’表4′[Month]<earlier('表4'[Month])&&(month('表4'[Month]) =6||month('表4'[Month]) =11))))
return y-z

捕获.PNG
成员
jianyao0105

filter连用了10+个 “||”运算符,dax studio跑了快2分钟 捂脸 。这种情况下,filtre函数或者“||”是最优的吗?等下还要再用个 and函数筛一下,估计更慢了。
公式主要想筛选出特定的10几个产品,在最近一天的有效消费(max日期消费>0)总金额。

成员
136****6402

A例中我理解的是filter对sales表的全部内容迭代筛选,保留满足条件的行,然后calculate以filter的结果赋予calculate第一参数中的sales表,最后计算表行数.
我表达应该不准确,其中可能还会有很多其他数据流动,只是想问问浅层这么理解的话有问题吗?

DAX 圣经

导读

初识 DAX

DAX 基础知识

DAX 原理

DAX 高级原理

基础函数类型

迭代函数

CALCULATE 函数

CALCULATE 调节器

基础表函数

条件判断函数

查找匹配函数

时间智能函数

统计类函数

投影函数

分组/连接函数

集合函数

其他函数