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

Question

我有这两个查询:

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 函数的定位——哪个更好，为什么？

笔记

因此，查看 Alex 引用的两篇 sqlbi 文章，我们可以执行以下任一操作以提高性能，但我仍然不确定 FILTER 函数是否应该在其他语法内部或外部发生:

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”功能:

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

笔记2

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

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

Answer 1

在您提供的两个选项中，我怀疑后者在计算上可能更有效。但是，两者都不是“最佳实践”。
根据 Best Practices Using SUMMARIZE and ADDCOLUMNS on sqlbi.com ,

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:

• 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 上的文章，在大多数用例中推荐使用较新的函数。