Multiple Relationships Between Tables in DAX

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 databaseIt 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]

image

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.

image

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.

image

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.

image

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.

L2WD banner ad

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.

image

The formula [Count of Orders] is simply as follows

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.

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.

image

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.

image

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).

image

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.
September 12, 2015 10:16 am

8 Comments

  • Kevin Zandee

    The relationships appears to allow users to do M to M, 1 to M in Power BI Desktop. Any comment on this?

    1. Yes, this is the same new functionality that is available in Excel 2016. I am holding out on having an opinion whether this is a good change or not, as I haven’t really used it yet. As long as performance and behaviour can be controlled, I guess I will be happy.

  • Anthony Newell

    Another, excellent one, you’ve demystified the dotted line for me
    Bedridden with flu catching up on all of this highly useful info

  • Is there any word on if the 2016 engine will natively support role playing dimensions?

    It’s very frustrating having to load multiple dimension tables. One of my SSAS tabular models currently has 6 identical dimension tables of 50,000 employees! It’s a performance killer.

    1. I am not aware of any changes to this. I am intrigued as to why you need 6 identical dimension tables. This implies that there must be 6 non identical key columns that need to join to the same fact table. Or am I missing something? Can you post a pic of the data model connections?

  • I am starting with power Bi and I found this very interesting, maybe my questions is a little dumb but I have to ask, the calendar table is like a predefined table? or it is necessary to create it in order to start working?

    Thank you!

Leave a Reply

Your email address will not be published. Required fields are marked *

*

*

Bring your data to life - Let's discuss how Matt can help!

Ten Things I Wish I Knew When I Started With Power Pivot.   Learn Power Pivot Without The Pain!

x