引言
报表一定是服务于具体的落地的业务。
大屏数据展示和看上去神乎其神的帕累托分析等可视化方式在一定程度上的确可以引人注目，但平时真正用它的时候也就是在分享会庆功会上时的锦上添花。大部分企业使用BI真正落地的一定是为了发现问题、攻坚克难的，或者对于复杂指标进行确定性一次构建，然后长久使用。
这是这些年我对于BI的理解。
本文结合一个特定行业的特定指标进行逐步分析、拆解、组装，阐述在进行复杂指标的度量值书写时，应该遵循的原则及步骤，思路与方法。认真阅读本文，一定会提升你对复杂度量值书写的信心。
问题阐述
教育培训类学校一个重要的考核指标是续班率。暑假、秋季、寒假、春季四个学期中，上一期的学员继续在下一期上课叫做续班。
续班的标准每个学校不太一样。比如学*思的标准是这样：
以寒假上课学员续班到春季为例，寒假上课的班级最后三次课的学员（共A人），最晚在春季的课程中第三次课要开始上课，且必须是同一个科目、同一个老师、同级或以上的课程类型，叫做续班，续班了B人。那么，B/A*100%就叫做续班率。
详细说明一下：
- 以上为数据表，数据表中包含课程类型、班级名称、科目、年级、教师姓名、课耗、学生姓名与上课日期；
- 寒假时间为2021年1月19日-2月16日；春季开始上课日期为3月8日；
- 所谓寒假最后三节课是指的每个班的最后三节课，因为每个班上课时间不同，因此最后三节课的时间也不同，因此不能直接筛选特定的三天；
- 所谓春季的前三节课也是每个班的前三节课，每个班上课时间不同，导致前三节课的时间也不相同；
- 春季班开课时间不能晚于4月5日，也就是说如果4月5日前没有开课，哪怕4月6日之后所有的学生都上课了，也不视为续班；
- 一名教师只能带一个科目，但是可以带不同年级的不同班名；
- 一名学生在同一个科目中只会选择一个班；
- 寒假的班名和春季的班名，命名方式并不相同，寒假班名第6个符号是“H”，春季班名6个符号是“C”。因此每一个班的名称是会随着学期而改变的；
- V课和拼课的级别不同，拼课一节课价格比V课贵1-2倍，等级高；比如寒假的V课学员在春季报名了V课或者拼课都可以算续班，而如果寒假的拼课在春季上了V课，那么就不算续班，很明显的；
- 学员在满足上述条件尤其是条件7的情况下，如果寒假是跟着学谦老师上课，而到了春季却跟着张三老师上课，那么也不算续班；因此续班一定是前后同一个老师同一个科目；
- 要考核的一定是课耗明细。因为会有这么一种情况发生：寒假上课的学员虽然报名了春季的课程，但是一直没有上课，这种情况下我们并不认为是续班；因此所有的数据都在这张'课耗明细'表上，所有的度量值也都是基于这张表；
- 寒假上课的学员在春季的第一堂课满足了以上全部的条件，但是从第二节课起就不再跟着这名老师上课了，我们也算作续班；但是在对于春季的考核中，我们会考核教师的春季学员出勤率。
- 考核教师的方式是以班为单位，比如尼古拉斯·赵四老师带了6个班，这6个班是分别计算续班情况，然后进行考核的。
这里只考虑的是对于单个教师的续班，这个计算过程与整个学校的续班情况分析一定是不同的。因为对于教师而言，他一定得保证学生一直是跟自己上课才能算续班，而对于学校而言，只要这个孩子还在上课就是续班，甚至寒假学习数学，春季学习物理也算续班。相对而言，学校的续班情况要好计算一些。
好了，背景说明就到这里了。
正文开始
首先我们在画布上拖拽教师姓名和班级名称的矩阵列：
根据第2条和第5条：
2.寒假时间为2021年1月19日-2月16日；春季开始上课日期为3月8日； 5.春季班开课时间不能晚于4月5日，也就是说如果4月5日前没有开课，哪怕4月6日之后所有的学生都上课了，也不视为续班；
我们需要选择两个时间范围来确定寒假时间和春季学期时间；很明显不能直接使用数据表中的日期列，我们需要建立一个日期表，并将日期表和数据表一对多关联起来；
建立日期表的方式有很多种，我们可以任选一种生成：
Power BI创建日期表的几种方式概览
我们要注意：在使用同一张日期表的情况下，不可能两个切片器都是日期表的日期列，也不能使用日期表的日期列和数据表的日期列，因为这两个已经关联了，导致的结果就是筛选为空：
因此，我们需要两张日期表：
一张主日期表A用来筛选出寒假的时间范围：2021年1月19日-2月16日；
一张次日期表B选出春季学期的范围为3月8日-4月5日。
主日期表A是要连接数据表的，而次日期表B不关联数据表，因为一旦都关联了数据表，两张日期表选择的日期范围不同，那么结果一定是空：
不过，因为我们可以通过让度量值对表进行ALL操作，所以如果两个日期表都关联了数据表，且选择的日期范围不同，带着度量值的表其实是可以显示出矩阵的。
方便写度量值起见，我们选择只有一个日期表对数据表关联，另一个不关联。
这个问题以及这种操作方式，我在这篇文章中有过详细说明：
【运营】任意两个时间段的复购率？Power BI一招帮你搞定
其实上面这篇文章对于大部分的应用场景才更有价值，也更有普遍性。
这样，模型我们就基本上构建好了：
下面对于以上的十几条说明展开讨论分析：
根据第10条：
10.学员在满足上述条件尤其是条件7的情况下，如果寒假是跟着学谦老师上课，而到了春季却跟着张三老师上课，那么也不算续班；因此续班一定是前后同一个老师同一个科目；
教师姓名已经是筛选器了，因此之后写的度量值不需要对教师姓名列有任何的改动：
根据第三条：
3.所谓寒假最后三节课是指的每个班的最后三节课，因为每个班上课时间不同，因此最后三节课的时间也不同，因此不能直接筛选特定的三天；
我们需要将寒假每个班级名称在选定的日期范围内找出日期最大的三天，并获取每个班的这三天所有上课的学员名单。
试着写出每个班最后三次上课日期的度量值：
代码语言：javascript复制日期.Last3DaysOfH =
VAR LAST3DAYS=TOPN(3,VALUES('课耗明细'[上课日期]),'课耗明细'[上课日期],DESC)
//获取每个班最后三次上课的日期
VAR LIST=CONCATENATEX(LAST3DAYS,[上课日期],"、")
VAR CLASSNAME=MAX('课耗明细'[班级名称])
RETURN IF(ISINSCOPE('课耗明细'[班级名称])&&MID(CLASSNAME,6,1)="H",LIST)
//最后用ISINSCOPE是因为后期会在姓名上直接使用另外一个度量值显示结果：
没问题。
下面获取每个班的这三天所有上课的学生姓名，基本上对上一个度量值适当修改即可得到：
代码语言：javascript复制学生列表.Last3DaysOfH =
VAR LAST3DAYS=TOPN(3,VALUES('课耗明细'[上课日期]),'课耗明细'[上课日期],DESC)
VAR STU=CALCULATETABLE(SUMMARIZE('课耗明细','课耗明细'[学生姓名]),LAST3DAYS)
VAR STULIST=CONCATENATEX(STU,[学生姓名],"、")
VAR CLASSNAME=MAX('课耗明细'[班级名称])
RETURN IF(ISINSCOPE('课耗明细'[班级名称])&&MID(CLASSNAME,6,1)="H",STULIST)显示结果：
（这篇文章其实断断续续写了三天，写着写着就想把中间的一些想法记录下来。前两篇文章其实就是为了这里服务的，有些时候需要进行一些明细的展示，却又不想全部暴露内容，即可使用这些办法：
从信息安全到如何在DAX中实现for循环
Power Query中的文本函数Text.Start/Middle/Range/End/At/Length）
根据第8条：
8.寒假的班名和春季的班名，命名方式并不相同，因此每一个班的名称是会随着学期而改变的；
班级名称是为了筛选寒假这个班里的所有学员，然而到了春季，班级名称是会变的，因此在确定是否续班到春季时一定要ALL掉班级名称这一列。
否则，因为春季不会有任何一名学生在寒假的班级中上课，会导致结果为空。
我们继续来写寒假最后三次课上课的学员在春季的同一教师、同一科目、同一班型中上课的度量值，对于每一步的每个变量的说明已经直接写在了度量值中：
代码语言：javascript复制学生列表.HtoFirst3DaysOfC =
//寒假后三次课的学员表，上文已经写过了
VAR Last3DaysOfH =TOPN(3,VALUES('课耗明细'[上课日期]),'课耗明细'[上课日期],DESC)
VAR StuOfH=CALCULATETABLE(SUMMARIZE('课耗明细','课耗明细'[学生姓名]),Last3DaysOfH )
//春季的班课名的表，使用了TREATAS函数将日期表2的date列
//TREATAS成课耗明细表的上课日期，无需建立关系就可以筛选
VAR CLASSLISTofC=
CALCULATETABLE(
SUMMARIZE('课耗明细','课耗明细'[班级名称]),
ALL('日期表'),
TREATAS(VALUES('日期表2'[date]),'课耗明细'[上课日期]),
FILTER(ALL('课耗明细'[班级名称]),MID('课耗明细'[班级名称],6,1)="C")
)
//获取春季各个班课名的前三次课的日期，使用GENERATE函数得到笛卡尔积
//注意ALL掉了'课耗明细'[班级名称]
VAR FIRST3CLASSDATEofC=
CALCULATETABLE(
GENERATE(
CLASSLISTofC,
TOPN(
3,
CALCULATETABLE(
VALUES('课耗明细'[上课日期]),
ALL('日期表'),
TREATAS(
VALUES('日期表2'[date]),
'课耗明细'[上课日期]
)
),
'课耗明细'[上课日期],
ASC
)
),
ALL('课耗明细'[班级名称])
)
//获取以上各个班的前三次课的上课学员名单
//注意ALL掉了'课耗明细'[班级名称]
VAR StuOfFirst3ClassDateOfC=
CALCULATETABLE(
SUMMARIZE('课耗明细','课耗明细'[学生姓名]),
FIRST3CLASSDATEofC,
ALL('日期表'),
TREATAS(VALUES('日期表2'[date]),'课耗明细'[上课日期]),
ALL('课耗明细'[班级名称])
)
//用INTERSECT函数将寒假和春季共同上课的学员找出来
VAR HtoC=INTERSECT(STUOFH,StuOfFirst3ClassDateOfC)
VAR STULIST=CONCATENATEX(HtoC,[学生姓名],"、")
VAR CLASSNAME=MAX('课耗明细'[班级名称])
RETURN IF(ISINSCOPE('课耗明细'[班级名称])&&MID(CLASSNAME,6,1)="H",STULIST)上面这个度量值包含的信息比较多，再复述一遍：
- 使用了TREATAS函数来关联两个表，而不是直接进行关联，灵活性增强，在这种情况下用这种方式效果非常好；
- 在计算春季的班名和学生明细时，需要ALL掉日期表，因为时间表切片器中不包含春季的时间；
- 同时需要ALL掉班级名称，因为这个班级名称是寒假的，前文也说过，用它去筛选春季的班级名称和学员一定为空；
- 使用了GENERATE函数生成了笛卡尔积；
- 使用了INTERSECT函数获取两个表的交集，此处如果我们还想将未续班的学员名单列出来，可以用EXCEPT函数，如VAR HnottoFIRST3DAYSofC=EXCEPT(STUOFH,STUofFIRST3CLASSDATEofC
- 使用ISINSCOPE函数可以让结果只在班级名称筛选时显示，而在教师行折叠时就不显示；
显示结果：
比如第一行的“P2008H10后进物理1班”中有5名学生，其中续班到春季的有2名学员。
对以上的度量值，我们只要稍微改动一下，用countrows替换掉concatenatex，即可得到这几个学生数，再使用DIVIDE函数就可以求续班率了：
这样我们就可以根据每一名教师的每一个班的续班情况进行奖惩了。
结论
教师的续班奖金是教师工资的重要组成部分，占了整体工资的较高比例，因此考核上也会更加严格。
通过制定准确无误的续班标准以及完全根据标准书写的度量值，不仅可以极大地减少重复性工作的工作量（就这个计算方法而言，能写出来的没几个，绝大部分都是挨个班手动筛选），更可以随时查看每一名教师的每一个班的续班情况，做到实时动态监测，以随时制定相关的策略保证续班。 虽然本文是针对教育培训学校这一特定的行业的续班这一特定指标进行说明，但是很多其他应用场景同样会遇到类似的问题，大家可以认真学习其中的思路，尤其是如何将复杂问题拆解为小问题、逐步解决的思路与能力，在工作中的方方面面都是有利的抓手。
That's it！
