带有和不带有FILTER的DAX计算功能 [英] DAX Calculate function with and without FILTER

问题描述

如果我们使用带有和不带有FILTER函数的 CALCULATE 函数的结果有什么区别？假设我们有两个度量值：

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 完全相同的结果？ / p>

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 的度量根据每一行的初始过滤器进行评估。

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屋！