DAX 第七篇:分组聚合

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

posted @ 2019-09-09 16:45 悦光阴 阅读(...) 评论(...) 编辑 收藏