【DAX】从简单分组聚合，到“条件聚合筛选”-喜乐君-敏捷BI布道师

最近，我想用几个典型的题目验证一下Excel、SQL、Tableau、DAX/POWER BI的分析能力。经过一番测试，更坚定了喜乐君“power BI向左、Tableau向右”的理念，过于复杂的代码逻辑，会阻碍业务用户的深度探索——技术成了“天花板”。

其中的几个题目如下：

1）不同客户的销售数量总和
2）2007年，不同客户的销售数量总和
3）2007年中，不同客户的销售数量总和，并仅保留数量总和大于100次的客户
4）仅保留数量总和大于100次的畅销产品的销售明细，分析不同客户的销售数量总和

从简单题目到复杂题目，工具之间的差异就有了明显的差异。喜乐君昨日就被上述第四个题目卡住了。基于上述的题目，喜乐君依次介绍DAX中的分组聚合、包含简单筛选的分组聚合、包含条件聚合的分组聚合。

一、DAX中的分组聚合及其简单筛选

分析的本质是聚合，或者说“分组聚合”。所以，Excel透视表、SQL聚合查询、Power BI/Tableau可视化的背后，都是聚合。

1）

最简单的场景下，我们可以使用DAX的SUMMARIZECOLUMNS函数完成分组聚合，如下所示：

EVALUATE
SUMMARIZECOLUMNS (
    Sales[CustomerKey], --  group by 
    "total qty", SUM(Sales[Quantity]) 
    )

这里，聚合的对象是Sales[Quantity]，它的分组依据是Sales[CustomerKey]，为什么没有类似于SQL 的FROM部分呢？因为DAX的字段默认是包含表名称的，所以来源表默认可以省略——在后续复杂案例中，它会非常重要。上述的查询对应如下的SQL：

SELECT CustomerKey，SUM(Quantity) --字段名称，前面没有表名称
FROM Sales
GROUP BY Sales[CustomerKey]

2）

问题的经典结构是“筛选范围、问题描述、问题答案”，分别对应“筛选、维度、度量”。如果在上面的DAX查询中，增加一个时间筛选范围，此时就需要理解完整的SUMMARIZECOLUMNS语法。如下所示：

在这里，函数使用了FILTER完成了“预先的明细筛选”，犹如SQL中的WHERE条件。

SELECT CustomerKey，SUM(Quantity) --字段名称，前面没有表名称
FROM Sales
WHERE YEAR('Order Date') = 2007  -- 明细筛选，对应DAX的fitler部分
GROUP BY Sales[CustomerKey]

二、进阶的筛选条件：聚合后的筛选（上）

第一部分筛选，是最简单的筛选形式——筛选在分组聚合之前完成，并且判断不包含聚合、排序等高级计算形式。在实际业务中，问题的复杂性首先是由筛选引起的，比如增加聚合的筛选，增加聚合后排序的筛选等等。这里介绍两种形式：

3）2007年，不同客户的销售数量总和，并仅保留数量总和大于100次的客户
4）仅保留数量总和大于100次的畅销产品的销售明细，分析不同客户的销售数量总和

3）

第3个案例中，“数量大于100次的客户”，是对分组聚合后的筛选，注意，这里的筛选对象（客户）和问题的分组对象一致，所以在SQL中，只需要在GROUP By之后添加Having即可完成，示例如下所示：

SELECT CustomerKey，SUM(Quantity) --字段名称，前面没有表名称
FROM Sales
WHERE YEAR('Order Date') = 2007  -- 明细筛选，对应DAX的fitler部分
GROUP BY Sales[CustomerKey]
HAVING SUM(Quantity)>100

参考SQL的逻辑，DAX也是如出一辙，只是在表达上受限于每个函数的功能，往往要使用嵌套才能完成。比如FIlter可以筛选但无法分组聚合，SummarizeColumns可以分组聚合又没有直接的筛选条件。

还是说回SummarizeColumns，它的第二个参数（filtertable）可以包含分组聚合前的明细筛选（比如2007年），但是不能包含分组聚合后的聚合筛选（比如上面的数量总和大于100）。此时，就要把SummarizeColumns的结果视为一个数据表，然后二次叠加筛选条件——把分组聚合的结果，视为一个新的明细表，然后做明细筛选。

直觉的方式，如下所示：

// 增加聚合后的筛选
EVALUATE
FILTER (
SUMMARIZECOLUMNS (
    Sales[CustomerKey],      -- 分组依据 group by 
    FILTER (                 -- 筛选范围 filter table  with condition 
        sales,
        YEAR ( Sales[Order Date] ) = 2007
    ),
    "total qty", SUM ( Sales[Quantity] ) -- 聚合字段
),
[total qty] >100 )

特别注意的是，筛选条件不能写写”total qty”>100，也不能写SUM ( Sales[Quantity] ) >100 ，对于FIlter而言，filter condition需要引用前面临时表的字段列名称，必须使用方括号才行。喜乐君在初学时走了很多弯路。

当然，如果把FILTER改为CALCULATETABLE，上面的表达式又不可以了。后续介绍二者差异。

比较一下SQL和DAX，你会发现DAX似乎把问题搞复杂了。这就是函数式语言相比SQL类编程语言的弱点，它简化了高级问题同时、让简单问题也变得复杂；当然相比之下，函数语言更容易入门，它把功能封装了。

三、进阶的筛选条件：聚合后的筛选（下）

真正的难点，是聚合后的筛选条件，并非以视图的分组字段为依据，而是指定问题之外的依据。比如：

仅保留数量总和大于100次的畅销产品的销售明细，分析不同客户的销售数量总和

4）

在这里，前置筛选中聚合的分组依据是产品（数量总和大于100的产品），问题的分组依据是客户（不同客户的数量总和）。严谨的说，聚合条件筛选依赖的详细级别与问题聚合依赖的详细级别不同，并且聚合条件筛选必须先于问题聚合发生——所以喜乐君称之为“预先聚合”。

在SQL中，早于GROUP BY同时还能引入聚合计算的部分，可以是FROM、Join，也可以是WHERE，因此可以用From和Join的表查询预先减少来源表范围，也可以用Where增加一个谓词逻辑（产品 IN () ），本质上都要使用一个包含聚合的子查询。从性能的角度看，喜乐君推荐使用FROM或Join完成；从理解的难易程度看，则推荐使用JOIN和Where。

如下所示：


SELECT  			-- 主视图的聚合 
	a."Customer Name",
	sum(a."Quantity")
from   public.sales a  -- 1000个 产品 
inner join  			--- 预先的聚合 __FIXED LOD 

	(SELECT 
	"Product Name",
	sum("Quantity")
	from public.sales 
	group by "Product Name"
	having sum("Quantity")>100  -- 300个产品 
	) b 
	on a."Product Name"= b."Product Name"

group by a."Customer Name"

在这里，INNER JOIN间接减少了后续GROUP BY和聚合的明细数量。

可视化有助于我们理解背后的逻辑关系，其中的过程如下所示：

在DAX中，上述的分组聚合对应SummarizeColumns函数，筛选对应FILTER函数，JOIN合并过程则可以对应NaturalInner Join函数。高手可以尝试完全对应在DAX中复刻，喜乐君还没有完成成功。好在有表模型，很多时候无需合并，模型关系就能完成我们预期的合并。

如下所示，喜乐君使用两次SummarizeColumns函数、一次FIlter筛选，借助于VAR简化嵌套关系，完成了类似逻辑。

当然，如果还想增加“2007年”筛选条件，就要考虑这个筛选是在“畅销产品”之前——近看2007年的畅销产品，还是在“畅销产品”筛选之后——即多年的畅销产品，在2007年的销售记录。在Tableau中，“上下文筛选器”（context filter）可以轻松完成类似过程；但在DAX中，计算的优先级要在代码中实现。

我们可以把上面DAX中第三步理解为SQL中FROM表和INNER JOIN整体。对应的SQL如下所示：

 
SELECT  			-- 主视图的聚合 
	a."Customer Name",
	sum(a."Quantity")
from   （ 
   select  * 
   FROM public.sales a1  -- 1000个 产品 
   inner  join  			--- 预先的聚合 __FIXED LOD 
	(SELECT 
	"Product Name",
	sum("Quantity")
	from public.sales 
	group by "Product Name"
	having sum("Quantity")>100  -- 300个产品 
	) b 
	on a1."Product Name"= b."Product Name"
  ） a 
group by a."Customer Name"

由于DAX的背后是表模型，我们可以把筛选条件一直附加到聚合之上，喜乐君参考DAX.guide的思路，也用了如下的方式完成：

 --问题： 2007年， 各个客户 的 数量总和 
--问题2: 2007年， 数量总和大于20次的大客户 （这个表述好吗）
DEFINE
    MEASURE Sales[Sales qty] =   -- 明细的计算，迭代聚合
        SUMX ( Sales, Sales[Quantity] )
    MEASURE Sales[SUM Monthly Sales] =
        VAR ProductQTY =
            ADDCOLUMNS (
                DISTINCT ( Sales[ProductKey] ), -- 维度是聚合分组依据， 
                "@ProductQTY", [Sales qty]    -- 各产品key 的数量总和
            )
        VAR FilteredSales =
            FILTER ( ProductQTY, [@ProductQTY] > 100 ) -- 对上述临时表做筛选，减少行
        VAR Result =
            -- Iterator required to aggregate the @MonthlySales column        
            SUMX ( FilteredSales, [@ProductQTY] )   -- 基于新的临时聚合明细表的聚合
        RETURN
            Result
EVALUATE
SUMMARIZECOLUMNS (
    Sales[CustomerKey],   -- 维度是聚合分组依据 
    "SUM Monthly Sales", [SUM Monthly Sales]  -- 对新
)

不得不说，这对于业务用户而言，太难了。

by 喜乐君

附加：方法3（错误）join的逻辑：还没有测试通过

   
var filtertable = filter('table01',
    [@total-qty]>100
    )

var jointable = NATURALINNERJOIN (sales,filtertable)


EVALUATE table01  
EVALUATE filtertable        
EVALUATE jointable 
EVALUATE 
  SUMMARIZECOLUMNS (
          Sales[CustomerKey],
          -- jointable[CustomerKey],
        "total",  SUMX(sales,RELATED (jointable[Quantity]))
        )

了解喜乐君的更多信息

订阅后即可通过电子邮件收到最新文章。

【DAX】从简单分组聚合，到“条件聚合筛选”

一、DAX中的分组聚合及其简单筛选

二、进阶的筛选条件：聚合后的筛选（上）

三、进阶的筛选条件：聚合后的筛选（下）

附加：方法3（错误）join的逻辑：还没有测试通过

分享到：

了解 喜乐君 的更多信息

了解喜乐君的更多信息