SUMMARIZECOLUMNS
返回一组组的摘要表。
语法
SUMMARIZECOLUMNS( <groupBy_columnName> [, < groupBy_columnName >]…, [<filterTable>]…[, <name>, <expression>]…)
parameters
术语 | 定义 |
---|---|
groupBy_columnName | 对基表的完全限定的列引用(表 [列]),其返回的表中包含不同的值。 每个 groupBy_columnName 列都与后续指定的列交叉联接(不同的表)或自动共存(相同的表)。 |
filterTable | 添加到指定为 groupBy_columnName 参数的所有列的筛选上下文中的表表达式。 筛选器表中存在的值用于在执行交叉联接/自动共存之前进行筛选。 |
name | 表示要用于指定的后续表达式的列名称的字符串。 |
表达式 | 任何返回单个值(而不是表)的 DAX 表达式。 |
返回值
一个表,它根据指定的分组包含所提供列的值的组合。 返回的表中仅包含符合后列条件的行:提供的表达式种至少有一个为其返回非空值。 如果一行的所有表达式的计算结果都为 BLANK/NULL,则该行不包含在返回的表中。
备注
此函数不保证结果的任何排序顺序。
不能在 groupBy_columnName 参数中多次指定某一列。 例如,下面的公式无效。
SUMMARIZECOLUMNS( Sales[StoreId], Sales[StoreId] )
在已计算的列或行级安全性 (RLS) 规则中使用时,不支持在 DirectQuery 模式下使用此函数。
筛选器上下文
请考虑下列查询:
SUMMARIZECOLUMNS (
'Sales Territory'[Category],
FILTER('Customer', 'Customer' [First Name] = "Alicia")
)
在此查询中,如果没有度量值,则 groupBy 列不包含 FILTER 表达式中的任何列(例如来自“客户”表的列)。 筛选器不应用于 groupBy 列。 “销售区域”和“客户”表可通过“分销商销售”事实数据表间接关联。 由于它们不是直接关联的,因此筛选器表达式不执行任何操作,并且 groupBy 列不受影响。
但是,对于以下查询:
SUMMARIZECOLUMNS (
'Sales Territory'[Category], 'Customer' [Education],
FILTER('Customer', 'Customer'[First Name] = "Alicia")
)
groupBy 包含一个受筛选器影响的列,该筛选器应用于 groupBy 结果。
使用 IGNORE
可通过在 BLANK/NULL 计算中省略特定表达式,使用 IGNORE 语法修改 SUMMARIZECOLUMNS 函数的行为。 未使用 IGNORE 返回 BLANK/NULL 的所有表达式的行将被排除,且与使用 IGNORE 的表达式的计算结果是否为 BLANK/NULL 无关。 IGNORE 只能在 SUMMARIZECOLUMNS 表达式中使用。
示例
SUMMARIZECOLUMNS(
Sales[CustomerId], "Total Qty",
IGNORE( SUM( Sales[Qty] ) ),
"BlankIfTotalQtyIsNot3", IF( SUM( Sales[Qty] )=3, 3 )
)
这将汇总 Sales[CustomerId] 列,为给定分组中的所有客户创建小计。 不使用 IGNORE,结果为:
CustomerId | 总数量 | BlankIfTotalQtyIsNot3 |
---|---|---|
A | 5 | |
B | 3 | 3 |
C | 3 | 3 |
使用 IGNORE,
CustomerId | 总数量 | BlankIfTotalQtyIsNot3 |
---|---|---|
B | 3 | 3 |
C | 3 | 3 |
已忽略所有表达式,
SUMMARIZECOLUMNS(
Sales[CustomerId], "Blank",
IGNORE( BLANK() ), "BlankIfTotalQtyIsNot5",
IGNORE( IF( SUM( Sales[Qty] )=5, 5 ) )
)
即使这两个表达式在某些行中都返回空白,它们也被包含在内,因为返回了空白的表达式均被忽略了。
CustomerId | 空白 | BlankIfTotalQtyIsNot5 |
---|---|---|
A | 5 | |
B | ||
C |
使用 NONVISUAL
NONVISUAL 函数将 SUMMARIZECOLUMNS 函数中的值筛选器标记为不影响度量值,但它仅应用于 groupBy 列。 NONVISUAL 只能在 SUMMARIZECOLUMNS 表达式中使用。
示例
DEFINE
MEASURE FactInternetSales[Sales] = SUM(FactInternetSales[Sales Amount])
EVALUATE
SUMMARIZECOLUMNS
(
DimDate[CalendarYear],
NONVISUAL(TREATAS({2007, 2008}, DimDate[CalendarYear])),
"Sales", [Sales],
"Visual Total Sales", CALCULATE([Sales], ALLSELECTED(DimDate[CalendarYear]))
)
ORDER BY [CalendarYear]
返回结果,其中 [直观总销售额] 是所有年份的总计:
DimDate[CalendarYear] | [销售额] | [直观总销售额] |
---|---|---|
2007 | 9,791,060.30 | 29,358,677.22 |
2008 | 9,770,899.74 | 29,358,677.22 |
与此相反,未使用 NONVISUAL 函数的相同查询:
DEFINE
MEASURE FactInternetSales[Sales] = SUM(FactInternetSales[Sales Amount])
EVALUATE
SUMMARIZECOLUMNS
(
DimDate[CalendarYear],
TREATAS({2007, 2008}, DimDate[CalendarYear]),
"Sales", [Sales],
"Visual Total Sales", CALCULATE([Sales], ALLSELECTED(DimDate[CalendarYear]))
)
ORDER BY [CalendarYear]
返回结果,其中 [直观总销售额] 是两个选定年份的总计:
DimDate[CalendarYear] | [销售额] | [直观总销售额] |
---|---|---|
2007 | 9,791,060.30 | 19,561,960.04 |
2008 | 9,770,899.74 | 19,561,960.04 |
使用 ROLLUPADDISSUBTOTAL
添加 ROLLUPADDISSUBTOTAL 语法后,通过向基于 groupBy_columnName 列的结果添加汇总行/小计行来修改 SUMMARIZECOLUMNS 函数的行为。 ROLLUPADDISSUBTOTAL 只能在 SUMMARIZECOLUMNS 表达式中使用。
有单个小计的示例
DEFINE
VAR vCategoryFilter =
TREATAS({"Accessories", "Clothing"}, Product[Category])
VAR vSubcategoryFilter =
TREATAS({"Bike Racks", "Mountain Bikes"}, Product[Subcategory])
EVALUATE
SUMMARIZECOLUMNS
(
ROLLUPADDISSUBTOTAL
(
Product[Category], "IsCategorySubtotal", vCategoryFilter,
Product[Subcategory], "IsSubcategorySubtotal", vSubcategoryFilter
),
"Total Qty", SUM(Sales[Qty])
)
ORDER BY
[IsCategorySubtotal] DESC, [Category],
[IsSubcategorySubtotal] DESC, [Subcategory]
返回下表,
类别 | Subcategory | IsCategorySubtotal | IsSubcategorySubtotal | 总数量 |
---|---|---|---|---|
True | True | 60398 | ||
Accessories | False | True | 36092 | |
Accessories | 自行车车架 | False | False | 328 |
Bikes | 山地自行车 | False | False | 4970 |
Clothing | False | True | 9101 |
有多个小计的示例
SUMMARIZECOLUMNS (
Regions[State], ROLLUPADDISSUBTOTAL ( Sales[CustomerId], "IsCustomerSubtotal" ),
ROLLUPADDISSUBTOTAL ( Sales[Date], "IsDateSubtotal"), "Total Qty", SUM( Sales[Qty] )
)
销售额按州省/自治区/直辖市、客户、日期分组,包含以下内容的小计:1. 按州省/自治区/直辖市、按日期所得的销售额 2. 按州省/自治区/直辖市、按客户所得销售额 3. 汇总客户和日期销售额,得到州销售额。
返回下表,
CustomerID | IsCustomerSubtotal | 状态 | 总数量 | Date | IsDateSubtotal |
---|---|---|---|---|---|
A | FALSE | WA | 5 | 2014/7/10 | |
B | FALSE | WA | 1 | 2014/7/10 | |
B | FALSE | WA | 2 | 2014/7/11 | |
C | FALSE | OR | 2 | 2014/7/10 | |
C | FALSE | OR | 1 | 2014/7/11 | |
true | WA | 6 | 2014/7/10 | ||
true | WA | 2 | 2014/7/11 | ||
true | OR | 2 | 2014/7/10 | ||
true | OR | 1 | 2014/7/11 | ||
A | FALSE | WA | 5 | true | |
B | FALSE | WA | 3 | true | |
C | FALSE | OR | 3 | TRUE | |
TRUE | WA | 8 | TRUE | ||
TRUE | OR | 3 | true |
使用 ROLLUPGROUP
与 SUMMARIZE 函数一样,ROLLUPGROUP 可与 ROLLUPADDISSUBTOTAL 一起使用,以指定要包含的摘要组/粒度(小计)(减少返回的小计行数)。 ROLLUPGROUP 只能在 SUMMARIZECOLUMNS 或 SUMMARIZE 表达式中使用。
有多个小计的示例
SUMMARIZECOLUMNS(
ROLLUPADDISSUBTOTAL( Sales[CustomerId], "IsCustomerSubtotal" ),
ROLLUPADDISSUBTOTAL(ROLLUPGROUP(Regions[City], Regions[State]), "IsCityStateSubtotal"),"Total Qty", SUM( Sales[Qty] )
)
仍按城市和省/自治区/直辖市分组,但在报告小计时汇总在一起返回下表,
状态 | CustomerId | IsCustomerSubtotal | 总数量 | 城市 | IsCityStateSubtotal |
---|---|---|---|---|---|
WA | A | FALSE | 2 | Bellevue | false |
WA | B | FALSE | 2 | Bellevue | false |
WA | A | FALSE | 3 | Redmond | false |
WA | B | false | 1 | Redmond | false |
OR | C | FALSE | 3 | Portland | false |
WA | true | 4 | Bellevue | false | |
WA | true | 4 | Redmond | false | |
OR | true | 3 | Portland | false | |
A | false | 5 | FALSE | ||
B | FALSE | 3 | true | ||
C | FALSE | 3 | TRUE | ||
true | 11 | TRUE |
上下文 SummarizeColumns
背景
直到 2023 年 2 月,SUMMARIZECOLUMNS 完全不支持上下文转换中的评估。 在该月之前发布的产品中,由于此限制,大多数度量值中都无法使用 SUMMARIZECOLUMNS,在任何上下文转换(包括其他 SUMMARIZECOLUMNS 语句)中都无法调用 MEASURE SUMMARIZECOLUMNS。
从 2023 年 2 月开始,在一些场景中支持上下文转换,但并非所有情况下都支持。 支持和受限制的情况如下所示:
SummarizeColumns 类型 | 单列外部筛选器 | 多列外部筛选器 | 外部 GroupBy 列 |
---|---|---|---|
仅使用 GroupBy 的 SummarizeColumns | OK | OK | OK |
使用筛选器/度量值的 SummarizeColumns | 确定 | ERROR | ERROR |
从 2024 年 6 月开始,我们将启用上下文 SummarizeColumns,允许在任何上下文转换中评估 SummarizeColumns,现在完全支持度量中的 SummarizeColumns:
SummarizeColumns 类型 | 单列外部筛选器 | 多列外部筛选器 | 外部 GroupBy 列 |
---|---|---|---|
仅使用 GroupBy 的 SummarizeColumns | OK | OK | OK |
使用筛选器/度量值的 SummarizeColumns | OK | OK | OK |
不过,此更新还包括对 SummarizeColumns 行为的更改,这可能会改变现有表达式的结果:
外部筛选器的 SelfValue 语义
我们引入了一个名为 SelfValue 的语义概念,此概念改变了外部表的筛选器与 SummarizeColumns 中的 GroupBy 列交互的方式。 此更改不允许来自其他表的筛选器影响 GroupBy 列,即使这些表通过筛选关系相关联也是如此。 演示此更改影响的一个示例涉及以下表达式:
CalculateTable(
SummarizeColumns(
'Reseller Sales'[ResellerKey],
'Reseller Sales'[ProductKey]
),
Treatas({(229)}, 'Product'[Product Key])
)
在此更新之前,TreatAs 筛选器将应用于 SummarizeColumns 中的 GroupBy 操作,利用 'Product'[Product Key] 和 'Reseller Sales'[ProductKey] 之间的关系。 因此,查询结果将仅包含 'Reseller Sales'[ProductKey] 等于 229 的行。 但是,更新后,SummarizeColumns 中的 GroupBy 列将不再按外部表中的列进行筛选,即使它们之间存在关系也是如此。 因此,在上面的示例中,GroupBy 列 'Reseller Sales'[ProductKey] 将不会按 'Product'[ProductKey] 列进行筛选。 因此,查询将包含 'Reseller Sales'[ProductKey] 不等于 229 的行。
如果希望保留以前的行为,则可以使用 Summarize 而不是 SummarizeColumns 重写表达式,如下所示:
CalculateTable(
SUMMARIZE(
'Reseller Sales',
[ResellerKey],
[ProductKey]
),
Treatas({(229)}, 'Product'[Product Key])
)
此重写表达式保留原始语义,其中 GroupBy 操作不受更新引入的 SelfValue 限制的影响。
Treatas 完全涵盖对 groupby 列的行验证
在此更新之前,在 SummarizeColumns 函数中,如果特定表中的所有 GroupBy 列都被同一个表中的单个 Treatas 筛选器完全覆盖,如下所示:
SummarizeColumns(
Geography[Country],
Geography[State],
Treatas(
{("United States", "Alberta")},
Geography[Country],
Geography[State]
)
)
上述查询的结果将包括 Treatas 筛选器中指定的任何行,无论它们是否有效。 例如,结果将是单行表("United States"、"Alberta"),即使 [Country] = "United States" 和 [State] = "Alberta" 的此类行也不存在于 'Geography' 表中。
此问题已知且已由更新解决。 更新后,将筛选出此类无效行,并且仅返回 GroupBy 表中的有效行。 因此,上述查询的结果将为空,因为“Geography”表中没有与指定的 [Country] 和 [State] 值匹配的有效行。
不允许在同一个表或群集上混合使用 Keepfilters/overriddefilters
最近的更新引入了一个临时限制,会触发一条错误消息,指出:
"SummarizeColumns filters with keepfilters behavior and overridefilters behavior are mixed within one cluster, which is not allowed. Consider adding keepfilters() to all filters of summarizecolumns."
当同一个表或群集中同时存在普通筛选器(替代现有筛选器)和指定了 KeepFilters 的筛选器时,会发生此错误。 例如:
Evaluate CalculateTable(
SummarizeColumns(
Product[Color],
KeepFilters(
TreatAs(
{( "Washington")}
, Geography[State]
)
),
TreatAs(
{("United States"), ("Canada")}
, Geography[Country]
)
)
,TreatAs({("Alberta")}, Geography[State])
,TreatAs({("Canada")}, Geography[Country])
)
在上述表达式中,“Geography”表上有两个筛选器:一个筛选器指定了 KeepFilters,另一个未指定。 这些筛选器与其他列上的外部筛选器重叠。 目前,这种配置是不允许的,因为内部将两个筛选器聚集到一个筛选器中,并且此类情况下系统无法确定群集筛选器的正确筛选器替代行为。
请注意,此限制是暂时性的。 我们正在积极开发解决方案,以便在将来的更新中消除此限制。 如果遇到此错误,我们建议根据需要添加或删除 KeepFilters 来调整 SummarizeColumns 中的筛选器,以确保对每个表执行一致的替代行为。