处理多对多关系,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/

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

(0)

相关推荐