文/陆文捷
物流供应链优化分析师，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/
示例数据基于星球案例文件，个人略作调整
联系客服
