过滤汇总列 [英] Filter SUMMARIZECOLUMNS

问题描述

如何为SUMMARIZECOLUMNS函数构造过滤表?

How to construct filter tables for SUMMARIZECOLUMNS function?

SUMMARIZECOLUMNS 具有以下模式:p>

The SUMMARIZECOLUMNS has the following pattern:

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.

推荐答案

您也可以像 PowerBI 一样使用 VAR 构造它们:

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

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

RETURN
SUMMARIZECOLUMNS (
    T[col],
    __MyFilterTable
)

哪个更有效取决于您的过滤复杂性，因此不一定存在一刀切"的规则.对于简单的表级过滤器，只需 FILTER 就足够了.我提醒您，过滤整个表 T 的第 1 行是个坏主意.仅过滤单个列的性能要高得多.当你过滤整个表时，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
)

同样，这是 PowerBI 在执行过滤器时使用的模式.

Again, this is the pattern that PowerBI uses when performing its filters.

顺便说一句，在顶部创建过滤器表与使用 SUMMARIZECOLUMNS() 内联创建它们不会对速度产生任何影响.避免像这里一般使用 CALCULATETABLE().

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")
)

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