Multiple Relationships Between Tables in DAX - Excelerator BI

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

Power BI DAX Book

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

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.

Power BI Online Training

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.

24 thoughts on “Multiple Relationships Between Tables in DAX”

  1. Dear Matt, good work there.
    my question is: how do I use both activate both active relationship and inactive relationship?
    Let’s say I need to display in a table elements both from the two relationship together… Thanks.

  2. It’s a great article.. simple to understand. I am deliberating on relationship design options as I have to show top 5 accounts by Opportunity (1st fact) and by Revenue (2nd fact) and show them for single selection of Date filter – by Year, Quarter, Month. I can’t seem to find much direction from various posts how I can connect date hierarchy to these facts. Should i consolidate to a single fact and use date hierarchy on one table? But, merged set will have issues with scale. Any suggestions on modelling is much appreciated.

    1. I think the best way to get help on this is to post at question at community.powerbi.com, provide some sample data and ask for help there. You can post a link back here if you like, but I only help out on the community when I get spare time (rarely these days)

  3. Thanks Matt, for the alternate option how we can sync 2 slicers that using different fields( e.g. one uses FY from Calendar and another FY from ShipCalendar) in one page? I thought Sync is replicating a slicer in different pages.

  4. If you want the user to select only 1 date table filter, then create 2 relationships between the one date table and the 2 date columns in your data table. One will be inactive and needs to be activated in the measure. Alternatively you can create 2 date tables and sync them in Power BI using Sync Slicers

  5. Hi Matt,
    Thanks for sharing this post. I was looking for the best practices for using Role dimensions in Power BI model. Using 2 dimensions sounds to give more flexibility in Power Bi reports but the issue comes when a slicers get used on Date e.g. for selecting FY as 2 slicers must be used (one for each Date dimension) and this makes the users confused.
    Do you have any suggestion to use one slicer to filter 2 Date tables?
    Cheers,

  6. Hi Matt

    I am new to power BI and Dax. I have been following your blogs and the articles.

    I can see what you are suggesting here about the multiple dates and date table. As you mentioned that we can achive the active and inactive relationship using DAX USERELATIONSHIP function and thus we dont have to duplicate the date table.

    The problem I have is that I have a task to build a data model and deploy it as a content pack on the service and the end user will use the model to create the reports. In the service you cant write DAX or create measures. So I cant use DAX ‘USERELATIONSHIP’ function. Does this mean that I have to create multiple DIm date table to link the dates?

    Can you please advice.

    Thanks

    1. correct, you can’t add measures in the service (yet). But this doesn’t imply you must use multiple tables, just that you must complete the data modelling work before publishing. So decided which approach is best and deploy that before publishing to the service. If USERELATIONSHIP is better for you, then do that.

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

  8. 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?

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

    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.

Leave a Comment

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

Scroll to Top