处理多对多关系，Excel中的PowerPivot与PowerBI哪个更方便？ / 开普饭

文/陆文捷

物流供应链优化分析师，Power BI爱好者，知乎:Beethovenist

在现实业务场景中，难免会遇到多对多的数据关系(Many to Many)，例如：

这个产品维度表部分产品同属于多种产品类别，它与事实表之间的关系就是多对多的关系，下面来看看在PowerPivot和PowerBI中，分别是如何处理多对多关系的？

PowerPivot

在Excel的PowerPivot数据模型中直接用[产品名称]或[产品类别]列与事实表建立关系会报错：

因为目前Power Pivot还不支持直接建立多对多关系，解决这一问题的常用方法是通过中间表（bridge table）解除多对多关系的耦合。

先分别建立产品和类别表，

产品-类别对应关系组成中间表，

建立这三张表对应的'产品(新)’→'产品类别’，'类别’→'产品类别’一对多模型关系，原来含多对多关系的产品表就此解耦，拆分为三张表，如下图所示：

新构建的完整模型关系：

建立度量值

Gross Margin = SUM ( '订单'[毛利] )

并按 '产品新’[产品名称]汇总：

不惊喜不意外，一切还是那么自然。

然后按照[产品类别]汇总数据：

咦？！每个类别的汇总数字都是总计数，显然不对了。再观察下数据模型，[产品类别]无法通过传递对事实表形成有效筛选，故在明细类别和总计行上都返回全局汇总结果。

故事当然不会再此终结，隆重有请CALCULATE和中间表来帮忙：

Gross Margin New =

CALCULATE( [Gross Margin] , '产品类别' )

结果如下：

将中间表作为CALCULATE的表筛选参数，开启了数据从'产品类别’→'产品新’表间的多端向一端的流向。

Excel 2016以后的版本新增了CROSSFILTER函数，更为容易理解的度量值写法可以实现同样效果：

Gross Margin CROSSFILTER=

CALCULATE(

[Gross Margin] ,

CROSSFILTER( '产品类别'[产品名称] , '产品新'[产品名称] ,BOTH )

)

结果如下：

从关系图来理解，解锁橙色箭头的数据筛选流向是正确汇总的关键。

那么在PowerBI中如何处理多对多关系呢？

Power BI

在PowerBI Desktop里，处理起来简单许多，无需建立中间表，也不用借助CROSSFILTER，Power BI天然支持多对多关系并且还提供不同的筛选选项：

设定多对多关系，选择数据筛选方向配合度量值轻松搞定：

相比Excel是不是方便不少？

当你建立多对多关系时，Power BI还会提示用户若非完全知晓计算意图和潜在影响，多对多关系并非数据建模的标准做法。

另外数据筛选流向有三种选择：

本文案例用正向和双向筛选都能返回正确结果，参考之前关于双向关系的文章，在此还是建议启用单一筛选流向。

总结

PowerPivot处理多对多关系

灵活使用中间表，掌握CROSSFILTER用法突破Excel无法建立多对多物理模型的限制。

PowerBI处理多对多关系

Power BI不仅能照搬Excel的处理方式，建模也为自由灵活，可以直接建立多对多的关系，但建立多对多的关系也要慎重选择哦~

从这里也可以看出，PowerBI的功能要比PowerPivot强大的多，建议大家直接学习和使用Power BI。

参考文章：

https://www.sqlbi.com/articles/many-to-many-relationships-in-power-bi-and-excel-2016/

示例数据基于星球案例文件，个人略作调整

处理多对多关系，Excel中的PowerPivot与PowerBI哪个更方便？