Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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).
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:
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!
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) -
To enable multiple active relationships just add (import) one of the tables a second time
Hello Maracles
Did you resolve your issue ?
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?
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
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.
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.
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.
Can you post a sample of your data here?
Proud to be a Super User!
User | Count |
---|---|
103 | |
88 | |
78 | |
71 | |
69 |
User | Count |
---|---|
113 | |
99 | |
97 | |
72 | |
68 |