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

焦棚子 2023-02-1017:15:51评论1,8924字数 3939阅读13分7秒阅读模式

焦棚子的文章目录

请在文末下载附件

 

一、背景

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

例如:

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

在前面的文章其实已经对此类问题做了相应的演示(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 去解决业务问题。

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

 

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

 

二、数据源及业务问题

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

1、数据源

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

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

2、关系

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

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

 

三、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,表格所在区间都是符合连续递增的。

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

 

环比上期

环比上期度量值中我们可以看到,这个是一个日期维度的比较,我们并没有用到时间智能函数,同时注意结果中的日期都是不连续的。如果使用时间智能函数其实相对还比较麻烦。所以窗口函数在 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

 

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

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

在 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】获得案例附件。

隐藏内容需要支付:¥5
立即购买 升级VIP

焦棚子
  • 除非特殊声明,本站文章均为焦棚子原创,转载请务必保留本文链接!
  • 《166_Power BI 窗口函数处理连续发生业务问题》链接:https://jiaopengzi.com/2902.html