I helped a couple of people this week on the topic of creating multiple relationships between two tables. There is not a lot of information on this topic on the web so I thought I would write about it here.
Power Pivot is not the Same a Relational DB
For those of you that have used SQL Server or MS Access, you will be aware that when using these relational database programs, it is perfectly acceptable to create multiple relationships between tables. Power Pivot is not a relational database but a reporting database. It needs data structured in a certain way to optimise the speedy process of finding the data needed and displaying it in your reports. Microsoft had to make compromises in the design to make this process fast, and one compromise is that there can only be 1 active relationship between 2 tables. In addition, the relationship MUST be one to many – no exceptions (although this is changing in Excel 2016 edit: and has already changed in Power BI Desktop as mentioned by Kevin in the comments). You can read more about 1 to many relationships and data shape in this article from my knowledge base.
Why would you need Multiple Relationships at All?
The most common scenario I have come across is when you have two date columns in your data table (eg Order Date and Ship Date) and you want to join both of these columns to the Calendar table. If there were only 1 date column, then you would typically set up your data like this (shown below). Note the relationship is between the Calendar[Date] and Sales[Order Date]
The immediate temptation is to then create a second relationship between Sales[Ship Date] and Calendar[Date]. When you do this, you will get the following behavior as shown below. Read More