在现实业务场景中,难免会遇到多对多的数据关系(Many to Many),例如: 这个产品维度表部分产品同属于多种产品类别,它与事实表之间的关系就是多对多的关系,下面来看看在PowerPivot和PowerBI中,分别是如何处理多对多关系的? PowerPivot 在Excel的PowerPivot数据模型中直接用[产品名称]或[产品类别]列与事实表建立关系会报错: 因为目前Power Pivot还不支持直接建立多对多关系,解决这一问题的常用方法是通过中间表(bridge table)解除多对多关系的耦合。 先分别建立产品和类别表, 产品-类别对应关系组成中间表, 建立这三张表对应的'产品(新)’→'产品类别’,'类别’→'产品类别’一对多模型关系,原来含多对多关系的产品表就此解耦,拆分为三张表,如下图所示: 新构建的完整模型关系: 建立度量值
并按 '产品 新’[产品名称]汇总: 不惊喜不意外,一切还是那么自然。 然后按照[产品类别]汇总数据: 咦?!每个类别的汇总数字都是总计数,显然不对了。再观察下数据模型,[产品类别]无法通过传递对事实表形成有效筛选,故在明细类别和总计行上都返回全局汇总结果。 故事当然不会再此终结,隆重有请CALCULATE和中间表来帮忙:
结果如下: 将中间表作为CALCULATE的表筛选参数,开启了数据从'产品类别’→'产品 新’表间的多端向一端的流向。 Excel 2016以后的版本新增了CROSSFILTER函数,更为容易理解的度量值写法可以实现同样效果:
结果如下: 从关系图来理解,解锁橙色箭头的数据筛选流向是正确汇总的关键。 那么在PowerBI中如何处理多对多关系呢? Power BI 在PowerBI Desktop里,处理起来简单许多,无需建立中间表,也不用借助CROSSFILTER,Power BI天然支持多对多关系并且还提供不同的筛选选项: 设定多对多关系,选择数据筛选方向配合度量值轻松搞定: 相比Excel是不是方便不少? 当你建立多对多关系时,Power BI还会提示用户若非完全知晓计算意图和潜在影响,多对多关系并非数据建模的标准做法。 另外数据筛选流向有三种选择:
本文案例用正向和双向筛选都能返回正确结果,参考之前关于双向关系的文章,在此还是建议启用单一筛选流向。 总结 PowerPivot处理多对多关系 灵活使用中间表,掌握CROSSFILTER用法突破Excel无法建立多对多物理模型的限制。 PowerBI处理多对多关系 Power BI不仅能照搬Excel的处理方式,建模也为自由灵活,可以直接建立多对多的关系,但建立多对多的关系也要慎重选择哦~ 从这里也可以看出,PowerBI的功能要比PowerPivot强大的多,建议大家直接学习和使用Power BI。 参考文章: https://www./articles/many-to-many-relationships-in-power-bi-and-excel-2016/ 示例数据基于星球案例文件,个人略作调整 |
|