带和不带 FILTER 的 DAX 计算函数 [英] DAX Calculate function with and without FILTER

问题描述

CALCULATE 函数使用和不使用 FILTER 函数的结果有何不同.假设我们有这两个度量:

What is the difference in results of CALCULATE function if we use it with and without FILTER function. Suppose we have those two measures:

Measure1 = CALCULATE([X], 'FactTable'[Color]="Red")

Measure2 = CALCULATE([X], FILTER('FactTable', 'FactTable'[Color]="Red")

更新

有没有办法通过使用其他函数来修改 Measure2，例如 ALL 或 ALLSELECTED，以便它准确返回结果与Measure1相同?

Update

Is there a way to modify Measure2 by using other functions, such as ALL, or ALLSELECTED, so that it would return exactly the same results as Measure1?

推荐答案

这两种措施不仅结果不同，而且获得这些结果的方式也不相同.

Not only the results, but also the way of obtaining those results for both measures will not be the same.

我创建了两个类似于您的示例的度量来测试这个:

I created two measures similar to your example to test this:

TestAvgNoFilter = CALCULATE([PrcAvg]; cal[ReadDate]=DATE(2018;05;23))
TestAvgFilter = CALCULATE([PrcAvg]; filter(cal; cal[ReadDate]=DATE(2018;05;23)))

当我简单地将它们都放入数据透视表而不使用任何其他字段或切片器时，当然它们都显示相同的结果:

When I simply throw both of them into pivot table without any additional fields or slicers, ofcourse both of them show the same result:

但是:

1. 使用 FILTER 具有显着的性能影响，这可以从存储引擎与公式引擎的查询计划和利用率中清楚地看出.它创建了额外的临时表，它需要与来自报告/数据透视表本身(行、列、切片器)的现有过滤器交互".您不会注意到单个单元格中的简单平均值，但如果您的 [x] 度量本身很复杂并且有许多初始"过滤器，则计算时间的差异可能会很大.

1. Using FILTER has significant performance impact, which can be clearly seen looking at query plans and utilization of Storage Engine vs Formula Engine. It creates additional temporary table that it needs to "interact" with already existing filters coming from report/pivot table itself (rows, columns, slicers). You won't notice anything for simple average value in single cell, but if your [x] measure itself is complicated and there are many of those "initial" filters, the difference in calculation time can be huge.

FILTER 保留并与初始过滤器上下文交互，而直接在 CALCULATE 中使用的过滤器表达式会忽略它.看看当我将 ReadDate 添加到数据透视表时会发生什么:

FILTER retains and iteracts with initial filter context, while filter expression used directly in CALCULATE ignores it. See what happens, when I add ReadDate to the pivot table:

这正是没有 FILTER 的度量更快的原因:它不关心列中的日期 - 它已经计算了一个真实"值，而具有 FILTER<的度量/code> 根据每一行的初始过滤器评估自己.

This is precisely why the measure without FILTER is faster: it doesn't care what dates are in columns - it already calculated one "true" value, while the measure with FILTER evaluates itself against initial filters for every row.

两列中的结果都可以被认为是正确的 - 这实际上完全取决于解释以及您如何命名度量;).

Results in both columns can be considered correct - it really all depends on interpretation and how you name the measures ;).

作为一般规则，我建议您在不需要时不要使用 FILTER.在真正需要的时候省电.

As a general rule I would suggest you don't use FILTER when you don't have to. Save it's power for when it's really needed.

这篇关于带和不带 FILTER 的 DAX 计算函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！