请在文末下载附件
一、背景
在生产经营的数据监控中，会有一类指标需要监控是否连续发生，从而根据其在设定区间中的连续频次来评价业务。
例如：
- 员工连续迟到天数。
- 销售金额连续上升或者下降。
- 用户连续登陆天数。
- 找出设定区间符合销售金额连续增长产品明细，诸如此类...
在前面的文章其实已经对此类问题做了相应的演示(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 去解决业务问题。
按照惯例还是先来看看结果
二、数据源及业务问题
首先感谢网友 @俊 提供数据，文中已经对数据进行了脱敏。
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 > 5
2、满足连续递增曾则表示表示 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 () )
RETURN
RESULT
四、关于连续发生事件的分步解析。
在 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 > 5
2、满足连续递增曾则表示表示 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区间只需要在在第一步中构造这样区间的上下文即可，在外部赋予它即可。
by焦棚子
请点击【立即购买】或者【升级VIP】获得案例附件。
评论