Forum Discussion
Multiple active relationship workaround
Is there a work around for Power BI not support multiple active relationships?
I have a situation where my Fact Table is related to another table in a one-to-many relationship. The fact table being the one, the other table being the many (a necessary data model design unfortuantely).
- FactTable has all of my orders including Order Date. Order date is related to my CalendarTable as an active relationship.
- VendorTable has all of my assignments. There can be multiple assignments per order. This table has a Vendor Date column which is the date the Vendor was assigned to the order. This date column is linked to my CalendarTable as an inactive table
I now need to produce a matrix visualisation that is based on the Vendor Date but which pulls in infomation from the FactTable, specifically the Vendor Fee. This is causing obvious problems. I can either:
- Create a table which is ordered based on the Vendor Date but doesn't include the Vendor Fee.
- Create a table which includes the Vendor Fee but does ordered based on the FactTable Order Date not my Vendor Date
Is there a way of having both? I know that I can create a new 'Related' column in my VendorTable however I've been taught that if you need to use RELATED() for a column there is probably a better way.
Hope this makes sense!
9 Replies
To enable multiple active relationships just add (import) one of the tables a second time
I use the USERELATIONSHIP expression for this without creating additional columns. Here is a link:
https://msdn.microsoft.com/en-us/library/hh230952.aspx?f=255&MSPPError=-2147217396
THanks kcantor. I actually use USERELATIONSHIP extensively but that doesn't work in my current scenario because that just switches the default relationship, I would need two active relationships to the same table at once.
I need my VendorTable to be actively connected to my DateTable whilst also actively being connected to my FactTable.In that case, I would use PowerQuery to 1. duplicate the vendor table, and 2. reduce the vendor table down to a single vendor id so that it could be used as a lookup table. Then I would simply use the original vendor table as another fact table and bridge it to the vendor id table and the date table. Bridge the Fact table to the date table and to the vendor id table. Make sure you populate the rows using the lookup table data and the values from the fact tables.
In an SSAS tabular on-line training course, I have found the following statement by Peter Myers (MVP) from Bitwise Solutions (one of the trainers on the course):
It is generally a good practice to only define active relationships. Instead of having active and inactive relationships consider creating duplicate tables. For example in an order table where you have order date and a ship date columns, create a Order Date table and a Ship Date Table.
I would expect this best practice advice would also apply to the power bi scenario?
Hello Maracles
Did you resolve your issue ?
To have a double active relationship between two tables:
1/ import the first table (e.g. First)
2/ import the second (e.g. Second)
3/ make the first relationship between tables 1 and 2
4/ import table 1 again (will be called First (2) )
5/ make the other relationship between 2 and your new 1 - First (2) -
