Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
maracles
Resolver II
Resolver II

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 9
ThierryD
New Member

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

ThierryD
New Member

To enable multiple active relationships just add (import) one of the tables a second time 

TaherBouzid
Frequent Visitor

Hello Maracles 

Did you resolve your issue ?

Anonymous
Not applicable

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?

kcantor
Community Champion
Community Champion

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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. 


kcantor
Community Champion
Community Champion

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




I've rearranged ym Data Model in the manner that you suggest however now I can't get any relationships to work. For example when I list the Lookup table ID and then try and bring in any columns from either table it simply lists every row with the same value.

Think my brain is fried for tonight so will have to try tomorro! Thanks. 

kcantor
Community Champion
Community Champion

Can you post a sample of your data here?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.