理解 SUMMARIZECOLUMNS

显示全部楼层 · 2021-2-21 18:51:55

SUMMARIZECOLUMNS 函数是一种更灵活、更高效的 SUMMARIZE 实现方式。在编写查询的时候，你可以优先考虑 SUMMARIZECOLUMNS。

SUMMARIZECOLUMNS

SUMMARIZECOLUMNS( <groupBy_columnName>, [< groupBy_columnName >] …, [<filterTable>]…,[<name>, <expression>] … )

复制代码

SUMMARIZECOLUMNS (
<group_by_column1>, [<group_by_column2>, ...],
[<filterTable1>, <filterTable2>, ...],
[<column1_name>, { <column1_expression> | IGNORE (<group_by_column> ) } ],
[<column2_name>, { <column2_expression> | IGNORE (<group_by_column> ) } ],
[...]
)

复制代码

SUMMARIZECOLUMNS 包含来自分组列生成的组合表。当至少一个表达式返回非空值的时候，整行才会包括在返回的表中。如果所有表达式对一行求值为空，则返回的表中不包括该行。

参数	属性	描述
GroupBy_ColumnName	可选可重复	用于分组的列或调用 ROLLUPGROUP 和 ROLLUPADDISSUBTOTAL 函数来计算分组小计的列
FilterTable	可选可重复	提供筛选上下文的表或表表达式. 表达式可以嵌入到 NONVISUAL 函数中，该函数将 SUMMARIZECOLUMNS 函数中的筛选条件标记为不影响度量值，仅应用于分组的列。
Name	可选可重复	添加的新列名称
Expression	可选可重复	新列的表达式

示例用法

例如，你可以这样使用 SUMMARIZECOLUMNS 编写 SUMMARIZE 的第一个示例：

EVALUATE
SUMMARIZECOLUMNS (
'Date'[Calendar Year],
"Quantity", SUM ( Sales[Quantity] )
)

复制代码

如果不定义任何新列，则结果不会筛选指定列中存在的任何值。

EVALUATE
SUMMARIZECOLUMNS (
'Date'[Calendar Year]
)

复制代码

等价形式

实际上，它的内部行为与 SUMMARIZE 有所不同。SUMMARIZECOLUMNS 从输出中删除所有列表达式为空值的行。例如，第一个示例的语法对应于以下内容：

EVALUATE
FILTER (
ADDCOLUMNS (
VALUES ( 'Date'[Calendar Year] ),
"Quantity", CALCULATE (
SUM ( Sales[Quantity] )
)
),
NOT ISBLANK ( [Quantity] )
)

复制代码

添加筛选器

SUMMARIZECOLUMNS 是生成查询时的一个常用函数。在不使用筛选表的情况下，由分组列为表达式提供筛选上下文。当使用筛选表（<filterTable1>）的时候，你可以假设其行为类似于 SUMMARIZE 的第一参数，即可以对通过多对一关系链访问的所有列进行分组。然而，使用 SUMMARIZECOLUMNS 中的筛选表可以做得更多，特别是在与双向交叉筛选相关的数据模型中。

如果你只想获取在销售表中有记录的年份，则需要将销售表指定为筛选上下文。但是，由于SUMMARIZECOLUMNS 必须将表筛选器置于函数内部，因此表筛选器不能作为 CALCULATE 或 CALCULATETABLE 的筛选器参数筛选 SUMMARIZECOLUMNS。故以下语法无效：

EVALUATE
CALCULATETABLE (
SUMMARIZECOLUMNS ( 'Date'[Calendar Year] ), //此筛选无效
Sales
)

复制代码

“SummarizeColumns() 和 AddMissingItems() 不能用于此上下文中”

相反，你可以指定一个或多个用作筛选的表，这些筛选表等价于在外部 CALCULATETABLE 中指定的筛选器参数：

EVALUATE
SUMMARIZECOLUMNS (
'Date'[Calendar Year],
Sales
)

复制代码

该查询返回一个三年的列表，该列表与第一个查询过滤后的列表相同，在第一个查询中，我们指定了一个聚合销售表 Quantity 列的表达式。

和 SUMMARIZE 一样，对 SUMMARIZECOLUMNS 的介绍也会涉及到扩展表的概念，在高级原理章节对此概念有详细解释。

筛选器不支持布尔表达式

在添加筛选表的时候需要注意，CALCULATE 和 CALCULATETABLE 的布尔表达式形式的筛选器参数不可直接用于 SUMMARIZECOLUMNS。你需要将其改写为表形式的筛选器：

EVALUATE
SUMMARIZECOLUMNS (
'Date'[Calendar Year],
Customer[Education] = "High School" //不支持布尔表达式
)
EVALUATE
SUMMARIZECOLUMNS (
'Date'[Calendar Year],
FILTER (
ALL ( Customer[Education] ),
Customer[Education] = "High School" //支持表筛选器
)
)

复制代码

其他注意事项

SUMMARIZE 接受来自外部的筛选上下文，而 SUMMARIZECOLUMNS 在这方面受到限制。这是两者的一个重要区别，特别是当你需要通过代码动态生成 DAX 查询时。建议在 SUMMARIZECOLUMNS 筛选器参数中直接加入筛选器，而不是依赖由 CALCULATE 或 CALCULATETABLE 定义的外部筛选上下文。不过，你仍然可以在这两个函数中使用布尔表达式过滤 SUMMARIZECOLUMNS 的结果。

SUMMARIZECOLUMNS 不需用表做为第一参数，你可以在定义用于分组的列之后选择性的指定一个或多个表作为筛选上下文的一部分，然后，你必须在接下来指定用于计值的筛选器，因为 SUMMARIZECOLUMNS 不具有外部筛选上下文。例如，以下查询返回日历年和产品类别的所有可能组合(总共 21 行)：

EVALUATE
SUMMARIZECOLUMNS (
'Date'[Calendar Year],
Product[Class]
)

复制代码

在之前的示例中，SUMMARIZECOLUMNS 具有与 CROSSJOIN 相同的行为。通常， SUMMARIZECOLUMNS 被用于更复杂的需求，而对于此类案例，你可以使用 CROSSJOIN

如果要求日历年和产品类别的组合至少在销售表存在一条记录，可以使用以下公式（结果共 9 行）：

EVALUATE
SUMMARIZECOLUMNS (
'Date'[Calendar Year],
Product[Class],
Sales
)

复制代码

下面的查询返回产品单价大于 3000 元的所有日历表年份和产品的组合（共 7 行）：

EVALUATE
SUMMARIZECOLUMNS (
'Date'[Calendar Year],
Product[Class],
CALCULATETABLE (
Product,
Product[Unit Price] > 3000
)
)

复制代码

最后一个查询同样返回产品单价大于 3000 元的所有年份和产品的组合，并且要求每个组合在销售表至少存在一条销售记录（共 3 行），在本例中，我们还将数量的求和添加为新列：

EVALUATE
SUMMARIZECOLUMNS (
'Date'[Calendar Year],
Product[Class],
CALCULATETABLE (
Sales,
Product[Unit Price] > 3000
),
"Quantity", SUM ( Sales[Quantity] )
)

复制代码

IGNORE 保留空行

如果把 Sales[Quantity]的总和添加为新列，你也许会使用之前来自产品表的筛选器。实际上，SUMMARIZECOLUMNS 默认删除在所有列表达式中具有空白结果的行。你可以使用 IGNORE 函数保留它们，如下例所示：

EVALUATE
SUMMARIZECOLUMNS (
'Date'[Calendar Year],
Product[Class],
CALCULATETABLE (
Product,
Product[Unit Price] > 3000
),
"Quantity", IGNORE (
SUM ( Sales[Quantity] )
)
)

复制代码

表达式计值环境（重要）

在 SUMMARIZECOLUMNS 中计值的表达式只有筛选上下文，而没有行上下文(SUMMARIZE 提供行上下文)。但是，你可以使用 VALUES 访问分组列，函数返回一个包含单行单列的表，将自动转换为标量值，如下面的示例所示。

EVALUATE
SUMMARIZECOLUMNS (
'Date'[Calendar Year],
Product[Class],
CALCULATETABLE (
Sales,
Product[Unit Price] > 3000
),
"Upper", UPPER (
VALUES ( Product[Class] )
),
"Quantity", SUM ( Sales[Quantity] )
)

复制代码

深入了解 SUMMARIZECOLUMNS

SUMMARIZECOLUMNS 不能在绝大部分度量值中使用，虽然在某些环境下可以得到正确结果，但你最好不要依赖这种方法。当你需要在度量值中执行分组和新建列时，最可靠的方式是SUMMARIZE+ADDCOLUMNS

为什么不支持度量值

SUMMARIZECOLUMNS 不支持上下文转换时发生的计算，这个特性使它无法在大多数度量值中使用。你可以在调用 CALCULATE 的度量值中使用它，但是遇到任何上下文转换的情况就会出错，而像 Excel 和 Power BI 生成的图表在计算时几乎都包含了上下文转换，仅少数 KPI 类型图表除外。

ROLLUPADDISSUBTOTAL

使用 ROLLUPADDISSUBTOTAL，你可以创建小计，同时添加标记列，显示当前行是否为指定列的小计。这是一种将 SUMMARIZE 中的 ROLLUP 函数和 ISSUBTOTAL 函数组合使用的简便方法。以下查询为日历表年份列添加小计：

EVALUATE
SUMMARIZECOLUMNS (
Product[Class],
Product[Weight Unit Measure],
ROLLUPADDISSUBTOTAL ( 'Date'[Calendar Year],
"Subtotal Year" ),
CALCULATETABLE (
Sales,
Product[Unit Price] > 3000
),
"Quantity", SUM ( Sales[Quantity] )
)

复制代码

可以多次调用 ROLLUPADDISSUBTOTAL，也可以在对 ROLLUPADDISSUBTOTAL 的一次调用中包含多组列引用和列名，两者结果相同。

ROLLUPGROUP

如果要为两列或更多列创建单个小计，可以使用 ROLLUPGROUP。下面的查询为 Class 和 Weight Unit Measure 列共同创建一个小计，也为每个年份创建小计。实际上，你会看到每年所有产品的小计，然后是所有年份和产品的总计：