| Many-to-Many relationships in PowerPivot |
| Written by Marco Russo |
| Monday, 07 December 2009 16:07 |
|
PowerPivot doesn’t have the capability of really understand a many-to-many (M2M) relationship between two tables. In a relational world, a many-to-many relationship is materialized using a bridge table that split this relationship in two separate one-to-many relationships between the two original tables and the bridge table. Apparently, we can do the same in PowerPivot, but the behavior is not the expected one. Some workaround is possible using DAX, but there are some undesirable side effects if we only use calculated column. As we will see, it is necessary to use calculated measures to get the best results. Consider two tables, Customers and Accounts. |
Author articles
- Create chained relationships in PowerPivot
- Using SSAS 2005/2008 as PowerPivot Data Source: Measures Are Imported as Text Columns
- Using SSAS 2005/2008 as PowerPivot Data Source: Direct MDX
- Using SSAS 2005/2008 as PowerPivot Data Source: Calculated Members
- Using SSAS 2005/2008 as PowerPivot Data Source: Query Designer
Most Popular
- Step by step guide on installing PowerPivot for SharePoint on a single machine
- List of suggested datasets to test PowerPivot
- List of PowerPivot DAX functions with description
- How to install PowerPivot for Excel and list of know issues
- Microsoft Virtual Lab: PowerPivot for Excel 2010 Introduction
- List of PowerPivot DAX functions (short)
- PowerPivot's impact on BI pros?
- Recorded webcast - A Preview to PowerPivot Server Best Practices
