Multiple Relationships Between Tables in DAX

Level: Beginners

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.

Note there are now 2 relationships, but one of them (the second one added) is a dashed line.  If you hover the mouse over this new relationship with the dashed line and then right click, you will see a menu pop up as shown below.

Notice that one of the menu options (#1 above) is “Mark as Active”.  This is a clue that the relationship with the dashed line is currently “inactive”.  When you hover the mouse over the other relationship with a solid line and right click, the menu (#1 below) now shows the option to “Mark as Inactive” indicating this one is currently active.

This is really the only information you will see in Power Pivot that refers to active and inactive relationships.  If you change the inactive relationship and make it active, then the active relationship will automatically switch to inactive.

How to Use Inactive Relationships

For this blog post, I will keep the relationship between Sales[Order Date] and Calendar[Date] as the active relationship and leave the other relationship as inactive.  I then set up the following Pivot Table – the Row Labels are coming from the Calendar[Date] column.

The formula [Count of Orders] is simply as follows

Count of Orders :=
COUNTROWS(Sales)

So now the problem is, how to count the orders that shipped on each day?  DAX allows you to override the active relationship and use the inactive relationship in your formulas instead.  To do this, you must use the DAX function USERELATIONSHIP inside a CALCULATE function as shown below.

Count of Orders Shipped :=
CALCULATE(
    [Count of Orders],
    USERELATIONSHIP(Calendar[Date], Sales[Ship Date])
)

The relationship MUST exist in the data model and be set as inactive for the above to work.  You can’t simply use the function USERELATIONSHIP and specify two columns and expect it to work – you must have the inactive relationship set up first.

The Pivot Table now will look like this, showing for each date, how many orders were taken and how many orders were shipped.

Alternatively You Could Load Two Calendar Tables

Another approach to this problem would be to simply load a second Calendar Table and call it ShipCalendar (or similar).  This would mean that you have 2 calendar tables and can use the one you need for the problem at hand.  Which approach you use is really down to personal preference and also how you are planning to report on your data.  In the Pivot Table above, you can see that it is easy to look at any date and see how many orders came in and how many orders were shipped out.  However the orders counted in these two columns are not the same orders (orders coming in on any date will ship either the next day or 2 days later in this test data).

If you want to be able to filter on orders that were taken on a certain date and then see when they shipped, you would need to change the pivot table layout like shown below.

The date (#1 above) comes from the Calendar Table and the dates on Rows (#2) come from the Sales[Shipped Date] column.  So this Pivot Table allows you to select an “Order Date” and see there were 9 orders, 4 shipped the next day and 5 shipped the day after.  The only problem with this approach is that the Shipped Date is coming from the Sales table.  Clearly it is working, but it is not best practice.  It is much better (particularly in large data models) to always use a Lookup Table to filter your Pivot Table (the filter on Rows in this case).  So better practice is to bring in a second ShipCalendar and remove Sales[Date] on Rows in the pivot table and replace it with ShipCalendar[Date].  (Note: I have now removed the inactive relationship so the DAX formulas above will not work any more).

The result with this new lookup table added will be the same as using the Sales[Date] column in the Pivot Table, but this second approach with a second lookup table will be much more performant and optimised for Power Pivot (which is important when you have a lot of data or 32bit Excel).

You can download my sample workbook here if you like.

A Final Word

In summary, you have a choice to either create multiple relationships between two tables, or alternatively you can bring in a second copy of your lookup table.

If you opt for multiple relationships:

• Only one of the relationships will be active
• The other relationships can be accessed by using the USERELATIONSHIP function inside of CALCULATE
• There is a limitation with this approach because you can only use a column from your lookup table once in your Pivot Tables.  In the example above this meant you could not use the Calendar[Date] column twice in the one Pivot Table.

If you opt for multiple lookup tables:

• You will have 2 sets of columns that CAN be used together in the one Pivot Table.
• You will need to be careful that users are not confused, and should consider naming the columns in the lookup tables with unique names like Calendar[Order Date] and ShipCalendar[Ship Date] for clarity.