DAX 有三个用于生成分组聚合数据的函数, 这三个函数有两个共同的特征: 分组列和扩展列.
分组列是用于分组的列, 只能来源于基础表中已存的列, 分组列可以来源于同一个表, 也可以来源于相关的列.
扩展列是由 name 和 expression 对构成的, name 是字符串, expression 是包含聚合函数的表达式.
在分组列和扩展列上, 这三个函数有各自独特的处理方式.
一, SUMMARIZE
SUMMARIZE 函数对相互关联的 Table 按照特定的一个字段 (分组列) 或多个字段, 进行分组聚合. 由于分组列是唯一的, 通过 SUMMARIZE 函数, 可以获得多列的唯一值构成的二维表:
SUMMARIZE(<table>, <groupBy_columnName>[, <groupBy_columnName>]...[, <name>, <expression>]...)
参数注释:
table: 必需参数, 表示主表, 可以是任何返回表的表达式.
groupBy_columnName: 可选参数, 表示分组列, 该列必须是 table 参数中的列, 或者相关联表中的列. 分组列必须使用列的完全限定名, 格式是 table[column], 分组列可以有 0 个, 1 个或多个.
name,expression: 可选参数, 表示自定义的汇总列 / 扩展列, 该参数对总是成对出现的, name 是 expression 计算结果的名称, expression 用于计算列的聚合值.
该函数的返回值是一个汇总表, 汇总表包含分组列和自定义的扩展列.
1, 获得多列的唯一值
分组列是唯一的, 可以不返回汇总列, 而只返回分组列, 这样得到的表是多列的唯一值.
- SUMMARIZE(ResellerSales
- , DateTime[CalendarYear]
- , ProductCategory[ProductCategoryName]
- )
2, 获得汇总数据
例如, 对数据表 ResellerSales , 按照字段 DateTime[CalendarYear] 和 ProductCategory[ProductCategoryName]分组, 计算 ResellerSales[SalesAmount]和 ResellerSales[DiscountAmount]的加和 .
- SUMMARIZE(ResellerSales
- , DateTime[CalendarYear]
- , ProductCategory[ProductCategoryName]
- , "Sales Amount", SUM(ResellerSales[SalesAmount])
- , "Discount Amount", SUM(ResellerSales[DiscountAmount])
- )
该函数利用 ResellerSales 和 DateTime,ProductCategory 之间的关系, 得到关联表数据(是一个中间临时表), 按照 DateTime[CalendarYear] 和 ProductCategory[ProductCategoryName] 对关联之后的数据进行分组, 分别计算销售和折扣的加和.
注意, ResellerSales 和 DateTime,ResellerSales 和 ProductCategory 必须显式存在关系, 否则, 不能用于分组列中.
3, 分组聚合的作用
第一是作为中间临时表, 为后续的计算提供数据; 第二是用于创建新表, 在 Modeling 菜单中, 通过 "New Table" 从 DAX 表达式中创建新的 Table:
参考文档: SUMMARIZE - groupping in data models (DAX - Power Pivot, Power BI)
4,ROLLUP 选项
ROLLUP 函数用于对分组列进行上卷操作, 该函数用于预定义多个分组集:
SUMMARIZE(<table>, <groupBy_columnName>[, <groupBy_columnName>]...[, ROLLUP(<groupBy_columnName>[,<groupBy_columnName>...])][, <name>, <expression>]...)
作用类似于 TSQL 的 rollup 函数, 例如, 对于 group by rollup(a,b) , 其表示的分组集是 group by (), group by (a), group by (a,b).
5,ROLLUPGROUP
ROLLUPGROUP 函数用于计算小计组. 如果把 ROLLUPGROUP 来代替 ROLLUP 函数, 那么 ROLLUPGROUP 通过向 groupBy_columnName 列的结果添加汇总行来产生和 ROLLUP 相同的结果. 但是, 在 ROLLUP 语法中添加 ROLLUPGROUP()可用于防止汇总行中的部分小计. 例如, ROLLUP(ROLLUPGROUP(A,B)), 分组集是 (A,B) 和():
- SUMMARIZE(ResellerSales_USD
- , ROLLUP(ROLLUPGROUP( DateTime[CalendarYear], ProductCategory[ProductCategoryName]))
- , "Sales Amount (USD)", SUM(ResellerSales_USD[SalesAmount_USD])
- , "Discount Amount (USD)", SUM(ResellerSales_USD[DiscountAmount])
- )
- 6,ISSUBTOTAL
只能用于 SUMMRIZE 函数中, 用于检查该列是否为小计组.
SUMMARIZE(<table>, <groupBy_columnName>[, <groupBy_columnName>]...[, ROLLUP(<groupBy_columnName>[,<groupBy_columnName>...])][, <name>, {<expression>|ISSUBTOTAL(<columnName>)}]...)
例如, 使用该函数检查 CalendarYear 和 ProductCategoryName 是否为小计组:
- SUMMARIZE(ResellerSales_USD
- , ROLLUP( DateTime[CalendarYear], ProductCategory[ProductCategoryName])
- , "Sales Amount (USD)", SUM(ResellerSales_USD[SalesAmount_USD])
- , "Discount Amount (USD)", SUM(ResellerSales_USD[DiscountAmount])
- , "Is Sub Total for DateTimeCalendarYear", ISSUBTOTAL(DateTime[CalendarYear])
- , "Is Sub Total for ProductCategoryName", ISSUBTOTAL(ProductCategory[ProductCategoryName])
- )
二, SUMMARIZECOLUMNS
该函数也用于分组聚合, 和 SUMMARIZE 函数的差异在于分组列之间的关系是非必需的, 分组列之间执行的交叉连接或自动存在.
SUMMARIZECOLUMNS( <groupBy_columnName> [, <groupBy_columnName>]..., [<filterTable>]...[, <name>, <expression>]...)
参数注释:
groupBy_columnName: 分组列, 该列必须使用列的完全限定名, 格式是 base_table[column], 该列必须是基础表中的现存列, 分组列可以有 0 个, 1 个或多个. 多个分组列之间的表不要求必须有关系, 对于不同表, 分组列之间是交叉连接(cross-join); 对于相同表, 分组列之间使用的是自动存在(auto-existed).
filterTable: 可选参数, 对分组列所在的基础表进行过滤, 过滤器表中存在的值用于在执行交叉连接 / 自动存在之前进行过滤.
name,expression: 可选参数, 表示自定义的汇总列, 该参数对总是成对出现的, name 是 expression 计算结果的名称, expression 用于计算列的聚合值.
返回值是汇总表, 包含分组列和自定义列, 返回的数据行中, 至少包含一个非空值, 如果在一个数据行中, 所有 expression 的结果都是 BLANK/NULL, 那么该行不包含在汇总表中.
1, 分组字段进行笛卡尔乘积
以下 DAX 按照 SalesTerritory 的字段 Category 和 Customer 的 Education 字段进行分组, 并对 Customer 表进行过滤:
SUMMARIZECOLUMNS ( 'SalesTerritory'[Category], 'Customer' [Education], FILTER('Customer', 'Customer'[First Name] = "Alicia") )
对过滤之后的数据进行汇总计算, 返回的结果是 Category 和 Eduction 的笛卡尔乘积.
2,IGNORE 选项
把包含 NULL/BLANK 的行过滤掉
SUMMARIZECOLUMNS(<groupBy_columnName>[, <groupBy_columnName>]..., [<filterTable>]...[, <name>, IGNORE(...)]...)
例如, 如果 Sum(Sales[Qty] )中包含一个 NULL/BLANK, 那么把该行从结果集中移除:
SUMMARIZECOLUMNS( Sales[CustomerId], "Total Qty", IGNORE( SUM( Sales[Qty] ) ), "BlankIfTotalQtyIsNot3", IF( SUM( Sales[Qty] )=3, 3 ) )
3, 其他选项
- NONVISUAL()
- ROLLUPADDISSUBTOTAL()
- ROLLUPGROUP()
三, GROUPBY
GROUPBY 函数除了不能再扩展列中使用 CALCULATE 函数之外, 和 SUMMARIZE 的用法相同:
GROUPBY (<table>, [<groupBy_columnName1>]..., [<name>, <expression>]... )
expression 参数中不能使用 CALCULATE 函数, CURRENTGROUP 函数只能用于最顶层的表扫描 (Table Scan) 操作.
GROUPBY 函数执行的操作是:
- #1: 从指定的表 (以及 "to-one" 方向的所有相关表) 开始
- #2: 按照所有的 GroupBy 列创建分组, 每一个分组代表一组数据行
- #3: 对于每一个分组, 评估要增加的扩展列(Extension column). 与 SUMMARIZE 函数不同, 不执行隐含的 CALCULATE, 并且不把该组放入到过滤器上下文中.
在该函数中, 可以调用 CURRENTGROUP 函数:
CURRENTGROUP()
该函数只能用于 GROUPBY 函数的 expression 参数中, 表示当前分组. CURRENTGROUP 函数不带参数, 仅支持作为以下聚合函数之一的第一个参数: AverageX,CountAX,CountX,GeoMeanX,MaxX,MinX,ProductX,StDevX.S,StDevX.P,SumX,VarX.S,VarX.P. 举个例子, 对 Sales 表, 按照 Country 和 Category 进行分组, 计算每个分组中 Price * Qty 的乘积之和.
- GROUPBY (
- Sales,
- Geography[Country],
- Product[Category],
- "Total Sales", SUMX( CURRENTGROUP(), Sales[Price] * Sales[Qty])
- )
参考文档:
- SUMMARIZE
- SUMMARIZECOLUMNS
- GROUPBY
- DAX function reference
来源: http://www.bubuko.com/infodetail-3190030.html