从数据到信息
从信息到洞察

理解 SUMMARIZECOLUMNS

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 可选
可重复
用于分组的列或调用 ROLLUPGROUPROLLUPADDISSUBTOTAL 函数来计算分组小计的列
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 不具有外部筛选上下文,因此不能将其嵌入到 CALCULATECALCULATETABLE 中调用。故以下语法无效:

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

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

相反,你可以指定一个或多个用作筛选的表,这些筛选表对应于在外部 CALCULATETABLE 中指定的筛选器参数:

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

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

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

不支持布尔表达式

在添加筛选表的时候需要注意,CALCULATECALCULATETABLE 的布尔表达式形式的筛选器参数不可用于 SUMMARIZECOLUMNS。你必须使用表形式的筛选器。因此,下面的查询产生语法错误:

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

其他注意事项

SUMMARIZE 接受来自外部的筛选上下文,而 SUMMARIZECOLUMNS 在这方面受到限制。这是两者的一个重要区别,特别是当你需要通过代码动态生成 DAX 查询时。你必须在 SUMMARIZECOLUMNS 筛选器参数中直接加入筛选器,而不是依赖由 CALCULATE 或 CALCULATETABLE 定义的外部筛选上下文。不过,你仍然可以使用 FILTER 函数过滤 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 列共同创建一个小计,也为每个年份创建小计。实际上,你会看到每年所有产品的小计,然后是所有年份和产品的总计:

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

ROLLUPADDISSUBTOTAL 内部的参数位置很重要,如果反转它们的顺序,你将得到一个为每个产品类别和重量单位计算所有年份的小计,逻辑会显著不同,只有总计保持不变。

8
说点什么

1000
 
鼓掌微笑开心憧憬爱你色并不觉得吃瓜doge二哈喵喵思考笑哭捂脸悲伤大哭抓狂汗偷笑打脸捂眼黑线问号晕拜拜闭嘴衰咒骂ok作揖
2 评论数
6 被回复的评论
3 订阅评论的人数
 
查看最近回复
查看最热评论
  订阅本文评论  
最新 最旧 得票最多
提醒
成员
139****3194

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

老师,请问 product[class]列(这里列可能有重复?还是被smmarizecolumns后已没有重复?),然后就像计算列一样,遇到calculateable转换为筛选上下文吗?筛选价格大于3000,且class为转换后的筛选条件,如果存在这样的销售记录,就和calendar year 进行generate,对吗?祝老师周末愉快!

成员
lyliuyouyang

学习了这个sunmarizecolumns函数以后,然后再再考虑这个函数能不能彻底代替summarize,我测试了以下代码:
DEFINE
VAR table1 =
UNION ( ‘人员1’, ‘人员2’ )
VAR table2 =
SUMMARIZE ( table1, ‘人员1′[工班], “人数”, COUNTA ( ‘人员1′[姓名] ) )
VAR table3 =
SUMMARIZECOLUMNS ( ‘人员1′[工班], table1, “人数”, COUNTA ( ‘人员1′[姓名] ) )

EVALUATE
table3
其中,table2能够返回合并后的数据。
在table3中,问题就来了,summarizecolmns因为第一参数不是表,表只能作为筛选参数,放在后面,但是实际测试过程中,把table1放进去完全没有起到作用,返回的就是人员1表里面的数据,和人员2一点关系都没有,但是对于这个合并的表里面的列,我也做不到引用,省略表名直接报错,写成table1[列名]或者’table1′[列名]也报错。请问,如果有合并表的情况在,用summarizecolumns怎么可以得到正确的结果?

DAX 圣经

导读

初识 DAX

DAX 基础知识

DAX 原理

DAX 高级原理

基础函数类型

迭代函数

CALCULATE 函数

CALCULATE 调节器

基础表函数

条件判断函数

查找匹配函数

时间智能函数

统计类函数

投影函数

分组/连接函数

集合函数

其他函数