三谈 Power BI 分组的业务需求

前言

之前已经和各位谈过 Power BI 分组的问题了，我的朋友已经完全掌握了，并且可以举一反三了，并以此为基础制作了一张人员加班时间统计的报表。

问题

需求

人员加班明细如下

员工ID	日期	加班时间
许安-10165	2019/1/2	4
许安-10165	2019/1/3	15
许安-10165	2019/1/4	17
许安-10165	2019/1/5	19
许安-10165	2019/1/6	5
许安-10165	2019/1/7	13
许安-10165	2019/1/8	22
许安-10165	2019/1/9	22
……	……	……

员工信息如下

员工ID	员工姓名	性别	职位
曾惠-14485	曾惠	女	管理
许安-10165	许安	女	HR
宋良-17170	宋良	男	行政
万兰-15730	万兰	女	行政
俞明-18325	俞明	男	财务
谢雯-21700	谢雯	男	财务
康青-19585	康青	女	财务
赵婵-10885	赵婵	女	HR
……	……	……

朋友对各个员工ID的平均加班时间进行分组操作，分成以下24组

组别	最小值	最大值
0~1	0	1
1~2	1	2
2~3	2	3
3~4	3	4
4~5	4	5
5~6	5	6
6~7	6	7
7~8	7	8
8~9	8	9
9~10	9	10
10~11	10	11
11~12	11	12
12~13	12	13
13~14	13	14
14~15	14	15
15~16	15	16
16~17	16	17
17~18	17	18
18~19	18	19
19~20	19	20
20~21	20	21
21~22	21	22
22~23	22	23
23~24	23	24

现在朋友已经可以按照前文完成相关分组，分组的 DAX 公式如下：

分组方式 =
VAR CurrentItem =
    SELECTEDVALUE ( '区间'[组别] )
VAR LeftValue =
    CALCULATE ( MIN ( '区间'[最小值] ), '区间'[组别] = CurrentItem )
VAR RightValue =
    CALCULATE ( MIN ( '区间'[最大值] ), '区间'[组别] = CurrentItem )
VAR mid_table =
    ADDCOLUMNS (
        VALUES ( '加班记录'[员工ID] ),
        "平均加班时间", CALCULATE ( AVERAGE ( '加班记录'[加班时间] ) )
    )
VAR ItemRange =
    FILTER ( mid_table, [平均加班时间] >= LeftValue && [平均加班时间] < RightValue )
RETURN
    COUNTROWS ( ItemRange )

完成后已经可以受打卡时间控制了：

p1_原始结果.gif

朋友的困扰

但是老板的需求不简单而且也永无至今，老板希望可以看到当前这些人的性别分布状况和职位分布状况，同时能够突出显示制定一个分组或者多个分组的人员性别和职位，但是现在的这个方法，组别的筛选完全无效。

p2_朋友的困扰.gif

解法

其实问题和简单，就是朋友使用的员工ID的计数不受分组的筛选，公式如下

员工ID的计数 = DISTINCTCOUNT('加班记录'[员工ID])

所以思路很简单就是需要构造一个能够受区间组别筛选的度量值即可，直接给出公式

人数 =
VAR CurrentItem =
    VALUES ( '区间'[组别] )
VAR basic_table =
    ADDCOLUMNS (
        VALUES ( '加班记录'[员工ID] ),
        "平均加班时间", CALCULATE ( AVERAGE ( '加班记录'[加班时间] ) )
    )
VAR mid_table =
    ADDCOLUMNS (
        basic_table,
        "分组", CALCULATE (
            VALUES ( '区间'[组别] ),
            FILTER ( ALL ( '区间' ), '区间'[最小值] <= [平均加班时间] 
                                                            && '区间'[最大值] > [平均加班时间] )
        )
    )
VAR ItemRange =
    FILTER ( mid_table, [分组] IN CurrentItem )
RETURN
    COUNTROWS ( ItemRange )

然后将该人数的度量值放入饼图，表格，和柱状图的值中即可。最终效果如下：

p3_最终效果.gif

我们可以发现对于组别，时间等筛选都是动态的在计算人数。

详解

1. 核心原理是什么？

核心原理就是构造一张虚拟表，用于接受组别的是筛选，以下代码即是构造虚拟表

VAR basic_table =
    ADDCOLUMNS (
        VALUES ( '加班记录'[员工ID] ),
        "平均加班时间", CALCULATE ( AVERAGE ( '加班记录'[加班时间] ) )
    )
VAR mid_table =
    ADDCOLUMNS (
        basic_table,
        "分组", CALCULATE (
            VALUES ( '区间'[组别] ),
            FILTER ( ALL ( '区间' ), '区间'[最小值] <= [平均加班时间] 
                                                            && '区间'[最大值] > [平均加班时间] )
        )
    )

其结果就是如下这张虚拟表

p4_中间表.png

2. 如何保证能够接受多个分组对其的筛选？

原理很简单，这里使用到了 in 这个函数，所属的代码块如下

VAR ItemRange =
    FILTER ( mid_table, [分组] IN CurrentItem )

各位战友对于类似 [分组] = CurrentItem这样的表达式已经驾轻就熟了，但是如果CurrentItem是一组数值，就无法使用这样的表达式，这时就必须要用in代替。

总结

最后，还是希望战友能够踊跃留言，与我交流，我们一起寻找 DAX 的边界。