应该在SUMMARIZE的内部还是外部使用FILTER？

本文介绍了应该在SUMMARIZE的内部还是外部使用FILTER？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有这两个查询：

EVALUATE FILTER ( SUMMARIZE ( 'Sales', Products[ProductName], 'Calendar'[CalendarYear], "Total Sales Amount", SUM ( Sales[SalesAmount] ), "Total Cost", SUM ( 'Sales'[TotalProductCost] ) ), Products[ProductName] = "AWC Logo Cap" ) ORDER BY Products[ProductName], 'Calendar'[CalendarYear] ASC

，并且：

EVALUATE SUMMARIZE ( FILTER ( 'Sales', RELATED ( Products[ProductName] ) = "AWC Logo Cap" ), Products[ProductName], 'Calendar'[CalendarYear], "Total Sales Amount", SUM ( Sales[SalesAmount] ), "Total Cost", SUM ( 'Sales'[TotalProductCost] ) ) ORDER BY Products[ProductName], 'Calendar'[CalendarYear] ASC

两者都返回以下内容：

两个查询之间的唯一区别是FILTER函数的位置-更好的做法以及原因？

The only difference between the two queries is the positioning of the FILTER function - which is better practice and why?

注意

所以看一下这两个sqlbi文章由Alex引用，我们可以执行以下任一操作以潜在地提高性能，但我仍然不确定FILTER函数应在其他语法的内部还是外部进行：

So looking at the two sqlbi articles referenced by Alex we can do either of the following to potentially make things more performant but I'm still unsure if the FILTER function should happen inside or outside the other syntax:

EVALUATE FILTER ( ADDCOLUMNS ( SUMMARIZE ( 'Sales', Products[ProductName], 'Calendar'[CalendarYear] ), "Total Sales Amount", CALCULATE ( SUM ( Sales[SalesAmount] ) ), "Total Cost", CALCULATE ( SUM ( 'Sales'[TotalProductCost] ) ) ), Products[ProductName] = "AWC Logo Cap" ) ORDER BY Products[ProductName], 'Calendar'[CalendarYear] ASC

并使用 SUMMARIZECOLUMNS功能：

And using the 'SUMMARIZECOLUMNS' function:

EVALUATE FILTER ( SUMMARIZECOLUMNS ( Products[ProductName], 'Calendar'[CalendarYear], "Total Sales Amount", SUM ( Sales[SalesAmount] ), "Total Cost", SUM ( 'Sales'[TotalProductCost] ) ), Products[ProductName] = "AWC Logo Cap" ) ORDER BY Products[ProductName], 'Calendar'[CalendarYear] ASC

note2

note2

看起来像SUMMARIZECOLUMNS有一个内置的FILTER参数，所以我猜这是防范性能问题的最佳方法：

Looks like SUMMARIZECOLUMNS has a built in FILTER parameter so I'd guess that this is the best way to go to guard against performance issues:

EVALUATE SUMMARIZECOLUMNS ( Products[ProductName], 'Calendar'[CalendarYear], FILTER ( 'Products', Products[ProductName] = "AWC Logo Cap" ), "Total Sales Amount", SUM ( Sales[SalesAmount] ), "Total Cost", SUM ( 'Sales'[TotalProductCost] ) ) ORDER BY Products[ProductName], 'Calendar'[CalendarYear] ASC

推荐答案

，我怀疑后者的计算效率可能更高。但是，这都不可能是最佳实践。

Of the two options you gave, I suspect the latter may be more efficient computationally. However, neither is likely "best practice".

根据在sqlbi上使用SUMMARIZE和ADDCOLUMNS的最佳实践，

您应该始终支持ADDCOLUMNS版本。经验法则是，除非由于以下至少一种情况而需要使用SUMMARIZE，否则决不要使用SUMMARIZE添加扩展列：

you should always favor the ADDCOLUMNS version. The rule of thumb is that you should never add extended columns by using SUMMARIZE, unless it is required due to at least one of the following conditions:

• 您想对一个或多个分组列使用ROLLUP以获得小计

• You want to use ROLLUP over one or more grouping columns in order to obtain subtotals

您正在扩展中使用非平凡的表表达式列，如您在本文后面的汇总和添加列中的过滤器上下文部分中所见

You are using non-trivial table expressions in the extended column, as you will see in the "Filter Context in SUMMARIZE and ADDCOLUMNS" section later in this article

还请查看他们在 SUMMARIZECOLUMNS 上的文章，该文章建议较新在大多数情况下都可以发挥作用。

Please also check their article on SUMMARIZECOLUMNS, which recommends the newer function in most use cases.