筛选SUMMARIZECOLUMNS [英] Filter SUMMARIZECOLUMNS

问题描述

如何为 SUMMARIZECOLUMNS 函数构造过滤器表？

How to construct filter tables for SUMMARIZECOLUMNS function?

SUMMARIZECOLUMNS 具有以下模式：

SUMMARIZECOLUMNS( 
    ColumnName1, ...
    ColumnNameN,
    FilterTable1,     -- my question concerns this line
    FilterTableN, 
    Name1, [measure1], 
    NameN, [measure2], 
)

我检查了以下3种模式是否有效。他们返回相同的结果，至少对于我使用的简单样本数据。

I have checked that the following 3 patterns work. They return the same results, at least for the simple sample data I used.

SUMMARIZECOLUMNS (
    T[col],
    FILTER( T, T[col] = "red" ) 
)

SUMMARIZECOLUMNS (
    T[col],
    CALCULATETABLE( T, T[col] = "red" ) 
)

SUMMARIZECOLUMNS (
    T[col],
    CALCULATETABLE ( T, KEEPFILTERS ( T[col] = "red" ) )
)

这些模式优于其他模式吗？

Is any of these patterns superior over the other?

参考： https://www.sqlbi.com/articles/introducing-summarizecolumns/

我对包含查询计划分析
或可靠来源链接的答案感兴趣。如果您在对来自
多个表的列进行分组时使用SUMMARIZECOLUMNS函数提到
，我将不胜感激。

I would be interested in an answer that contains a query plan analysis or link to credible source. I would be grateful if you mentioned using the SUMMARIZECOLUMNS function when grouping columns from multiple tables.

推荐答案

您还可以使用VAR像PowerBI那样构造它们：

You can also construct them the way PowerBI does, using VAR:

VAR  __MyFilterTable = FILTER( T, T[col] = "red" ) 

RETURN
SUMMARIZECOLUMNS (
    T[col],
    __MyFilterTable
)

哪种方法更有效取决于您过滤的复杂程度，因此不一定有一个大小适合所有人的规则。对于简单的表级过滤器，仅FILTER就足够了。请注意，在第1行中过滤整个表格T的想法不是一个好主意。仅过滤单个列的性能更高。当您过滤整个表时，DAX会在内存中实现整个表，而以下代码仅实现T [col]的一个值：

Which is more efficient will depend on the complexity your filtering, so there is no "one size fits all" rule necessarily. For a simple table level filter, just FILTER will suffice. I caution you that Line 1, where you're filtering the entire table T, is a bad idea. It's much more performant to only filter a single column. When you filter the entire table, DAX materializes the entire table in memory, while the following just materializes the one value of T[col]:

VAR  __MyFilterTable = FILTER( ALL(T[col]), T[col] = "red" ) // This is better.

RETURN
SUMMARIZECOLUMNS (
    T[col],
    __MyFilterTable
)

从概念上讲，您甚至可以做得更好。您基本上可以告诉DAX：我知道这是一个值，所以甚至不必在表中查找它。只需为我创建一个表并将其视为已过滤它就可以了。像这样：

You can do even better than that, conceptually. You can basically tell DAX, "I know this is a value, so don't even look in the table for it. Just make me a table and treat it as though I filtered it. Like this:

VAR  __MyFilterTable = TREATAS ({"red"}, T[col] )

RETURN
SUMMARIZECOLUMNS (
    T[col],
    __MyFilterTable
)

再次，这是

BTW，在顶部创建过滤器表与使用SUMMARIZECOLUMNS（）内联创建它们没有任何区别

BTW, Creating the filter tables a the top vs. creating them inline with SUMMARIZECOLUMNS() won't make any difference for speed. Avoid using CALCULATETABLE() as you've done here generally.

您也可以执行此操作，尽管您不太可能看到速度普遍提高：

You can also do this as well, though you aren't likely to see a speed increase generally:

CALCULATETABLE(
    SUMMARIZECOLUMNS (
        T[col]
    ),
    KEEPFILTERS(T[col] = "red")
)

这篇关于筛选SUMMARIZECOLUMNS的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！