166_技巧_Power BI 窗口函数处理连续发生业务问题

166_技巧_Power BI 窗口函数处理连续发生业务问题

一、背景

在生产经营的数据监控中，会有一类指标需要监控是否连续发生，从而根据其在设定区间中的连续频次来评价业务。

例如：

• 员工连续迟到天数。
• 销售金额连续上升或者下降。
• 用户连续登陆天数。
• 找出设定区间符合销售金额连续增长产品明细，诸如此类…

在前面的文章其实已经对此类问题做了相应的演示(https://jiaopengzi.com/392.html)。

本次我们使用 Power BI 在 2022 年 12 月份更新的窗口函数来处理：找出设定区间符合销售金额连续增长产品明细 的问题。关于窗口函数的信息可以参考 Jeffrey Wang 在博客(https://pbidax.wordpress.com/2022/12/15/introducing-dax-window-functions-part-1/) 中的说明，已经非常的详细。本文我们将不花篇幅去讲解窗口函数了。我们重点来讲讲通过 DAX 去解决业务问题。

按照惯例还是先来看看结果

Power BI 公共 web 效果：https://demo.jiaopengzi.com/pbi/166-full.html

二、数据源及业务问题

首先感谢网友 @俊 提供数据，文中已经对数据进行了脱敏。

1、数据源

数据源非常简单，就一个销售数据表，其中包含日期、产品ID、销售金额

2、关系

建模必备的日期表，同时把 产品ID 单独拎出来做维度表，建立表间关系。

三、DAX

1、销售金额

基础度量没有什么好说的。

0000_销售金额 = SUM ( '销售数据'[销售金额] )

2、是否符合要求

0001是否符合要求 = 
VAR ROW_MAX = /*当前事实表行数。*/COUNTROWS ( '销售数据' )
VAR T1 =/*准备好窗口函数需要的表格。*/SUMMARIZE ( ALLSELECTED ( '销售数据' ), '销售数据'[产品ID], 'A00_Calendar'[C01_Dates], '销售数据'[销售金额] )
VAR T2 =/*  1、根据产品ID和日期提前做好排序。2、需要注意这里使用的是绝对应用，之所以使用 N，是因为不会有比 N 更大的行数。3、因为要对每个 SKU 单独排序所以要使用 PARTITIONBY。*/WINDOW (1,ABS,ROW_MAX,ABS,T1,ORDERBY ( '销售数据'[产品ID], ASC, 'A00_Calendar'[C01_Dates], ASC ),KEEP,PARTITIONBY ( '销售数据'[产品ID] ))
VAR T3 =/*使用 OFFSET 偏移一行，实现错位，拿到 N+1 的数据。*/ADDCOLUMNS (T2,"@N+1",CALCULATE ([0000_销售金额],T1,OFFSET (1,T2,ORDERBY ( '销售数据'[产品ID], ASC, 'A00_Calendar'[C01_Dates], ASC ),KEEP,PARTITIONBY ( '销售数据'[产品ID] ))))
VAR T4 =/*错位后，N+1 大于成交金额金额即为满足增加。*/ADDCOLUMNS ( T3, "@是否递增", IF ( [@N+1] > [销售金额], 1, 0 ) )
VAR T5 =/*1、计算每个 SKU 记录数量 @count。2、计算每个 SKU 总共与多少是符合递增的。*/SUMMARIZE (T1,[产品ID],"@count", VAR SKU = [产品ID] VAR T = FILTER ( T1, [产品ID] = SKU ) RETURN COUNTROWS ( T ),"@growth", VAR SKU = [产品ID] VAR T = FILTER ( T4, [产品ID] = SKU ) RETURN SUMX ( T, [@是否递增] ))
VAR T6 =/*1、依据业务的要求，需要满足记录数大于5，则 @count > 52、满足连续递增曾则表示表示 SKU 记录数量 @count 和 满足递增爽 @growth 差 1 即可，第一期是没有计算的递增的。*/FILTER ( T5, [@count] - 1 = [@growth] && [@count] > 5 )
VAR TF =/*最有是要找出这些 SKU 那么输出表，在不建立计算表的情况下吗，那么就把使用度量值的设置是否等于1来实现筛选即可*/IF ( VALUES ( '产品表'[产品ID] ) IN SELECTCOLUMNS ( T6, "产品ID", [产品ID] ), 1, 0 )
RETURN/*符合要求度量值结果为 1 ，不符合要求度量值结果为 0*/TF

在上图 166-1 中，我们可以看到业务需求如下：找出成交金额随着日期连续大于5期递增的产品ID；找出产品ID后，计算出最小粒度日期环比(说明数据源的的产片销售日期不一定连续)

注意这里的度量值是否符合要求，我们是放到了切片器的视觉对象筛选器中，切片器的字段是产品ID。这里利用了度量值是否等于1的结果来判断是否符合要求。

我们通过切片器切换可以看到都是符合要求产品ID，表格所在区间都是符合连续递增的。

环比上期

环比上期度量值中我们可以看到，这个是一个日期维度的比较，我们并没有用到时间智能函数，同时注意结果中的日期都是不连续的。如果使用时间智能函数其实相对还比较麻烦。所以窗口函数在 DAX 中是一把利刃。

0002_环比上期 = 
VAR FZ = [0000_销售金额]
VAR T0 =SUMMARIZE ( ALLSELECTED ( '销售数据' ), '产品表'[产品ID], 'A00_Calendar'[C01_Dates] )
VAR FM =CALCULATE ([0000_销售金额],OFFSET ( -1, T0, ORDERBY ( 'A00_Calendar'[C01_Dates], ASC ) ))
VAR _PERCENT =DIVIDE ( FZ - FM, FM )
VAR RESULT =IF ( HASONEFILTER ( A00_Calendar[C01_Dates] ), _PERCENT, BLANK () )
RETURNRESULT

四、关于连续发生事件的分步解析。

在 DAX Studio 中使用如下 DAX 代码去分布理解，其实也就是上述 “是否符合要求” 的度量值中的核心部分。

在结合注释理解连续发生问题的处理过程。就是把这类连续发生问题，首先通过排序来抽象成 1 或者 0，当然也可以抽象成 YES 或者 NO， 抽象为 1 和 0 在 DAX 计算中会更加方便。

其实这里面最主要的问题是在 DAX 中生成的过程表要能排序是非常困难的，当然也可以结合前面的文章来看(https://jiaopengzi.com/392.html),现在有了窗口函数也就更加得心应手了。

DEFINE
VAR ROW_MAX = /*当前事实表行数。*/COUNTROWS ( '销售数据' )
VAR T1 =/*准备好窗口函数需要的表格。*/SUMMARIZE ( ALLSELECTED ( '销售数据' ), '销售数据'[产品ID], 'A00_Calendar'[C01_Dates], '销售数据'[销售金额] )
VAR T2 =/*  1、根据产品ID和日期提前做好排序。2、需要注意这里使用的是绝对应用，之所以使用 N，是因为不会有比 N 更大的行数。3、因为要对每个 SKU 单独排序所以要使用 PARTITIONBY。*/WINDOW (1,ABS,ROW_MAX,ABS,T1,ORDERBY ( '销售数据'[产品ID], ASC, 'A00_Calendar'[C01_Dates], ASC ),KEEP,PARTITIONBY ( '销售数据'[产品ID] ))
VAR T3 =/*使用 OFFSET 偏移一行，实现错位，拿到 N+1 的数据。*/ADDCOLUMNS (T2,"@N+1",CALCULATE ([0000_销售金额],T1,OFFSET (1,T2,ORDERBY ( '销售数据'[产品ID], ASC, 'A00_Calendar'[C01_Dates], ASC ),KEEP,PARTITIONBY ( '销售数据'[产品ID] ))))
VAR T4 =/*错位后，N+1 大于成交金额金额即为满足增加。*/ADDCOLUMNS ( T3, "@是否递增", IF ( [@N+1] > [销售金额], 1, 0 ) )
VAR T5 =/*1、计算每个 SKU 记录数量 @count。2、计算每个 SKU 总共与多少是符合递增的。*/SUMMARIZE (T1,[产品ID],"@count", VAR SKU = [产品ID] VAR T = FILTER ( T1, [产品ID] = SKU ) RETURN COUNTROWS ( T ),"@growth", VAR SKU = [产品ID] VAR T = FILTER ( T4, [产品ID] = SKU ) RETURN SUMX ( T, [@是否递增] ))
VAR T6 =/*1、依据业务的要求，需要满足记录数大于5，则 @count > 52、满足连续递增曾则表示表示 SKU 记录数量 @count 和 满足递增爽 @growth 差 1 即可，第一期是没有计算的递增的。*/FILTER ( T5, [@count] - 1 = [@growth] && [@count] > 5 )
EVALUATE
T1
EVALUATE
T2
EVALUATE
T3
EVALUATE
T4
EVALUATE
T5
EVALUATE
T6

五、总结

1、本文使用 DAX 窗口函数处理连续发生问题的排序，也可以在 pq 中去处理，也能在 sql 一步到位。方式和工具还是比较多；一切工具都是为了业务服务的。

2、窗口函数在不连续的日期对比或者非日期的对比中都能实现， 极大的增加 Power BI 了数据的灵活性。

3、本文案例中，我们需求的是整个数据表为监控的区间，大家要能做到举一反三，如果需要监控不同日期区间或者不同产品ID区间只需要在在第一步中构造这样区间的上下文即可，在外部赋予它即可。

附件下载

https://jiaopengzi.com/2902.html

视频课

https://jiaopengzi.com/all-course

by 焦棚子