Forum Discussion
Post PatronUnique Values and Relationship issue with blank values looking like duplicates?
I've got 2 tables I'm trying to create a relationship with. One table is called "Account", the other "Inventry"
Account[UniqueNumberSupplier] and Inventry[Supplier] are the two I'm trying to create a relationship with.
The Account[UniqueNumberSupplier] has a bunch of blank values in it coming from the formula
UniqueNumberSupplier = if(Account[AType]="P",Account[AUnique],blank())
as the account table has customers AND suppliers in it. I did this formula to strip out just the suppliers so I could create the relationship to the Inventry[Supplier]
I'm assuming its the blank() that's making the issue with multiple blank values. These show up as NULL in the relationship tool. Any numbers that are in the table that are not blank, are unique and not duplicated.
Do I need to trim my formula so that I don't have these issues? Or do I need to make a new table soely called "Supplier" so that there are no "blanks" in there?
Hi unclejemima,
In your scenario, I’m afraid you could not create a relationship between the two columns. Because duplicate values are both in your ‘Account’[UniqueNumberSupplier] column and ‘Inventry’[Supplier] column. Power BI doesn’t support this many-to-many relationship. You can view related links about this: https://docs.microsoft.com/en-us/power-bi/desktop-create-and-manage-relationships#cross-filter-direction
I suggest you to create a bridge table that has distinct value of ‘Inventry’[Supplier] from both tables. Then you can create relationships among these three tables N:1:N (Account table <-> Bridge table <-> Inventry table).
Regards,
Daniel He
3 Replies
v-danhe-msftMicrosoft Employee
Hi unclejemima,
In your scenario, I’m afraid you could not create a relationship between the two columns. Because duplicate values are both in your ‘Account’[UniqueNumberSupplier] column and ‘Inventry’[Supplier] column. Power BI doesn’t support this many-to-many relationship. You can view related links about this: https://docs.microsoft.com/en-us/power-bi/desktop-create-and-manage-relationships#cross-filter-direction
I suggest you to create a bridge table that has distinct value of ‘Inventry’[Supplier] from both tables. Then you can create relationships among these three tables N:1:N (Account table <-> Bridge table <-> Inventry table).
Regards,
Daniel He
Hey, v-danhe-msft.
Can we create a bridge table that only has one column - the key?
In the visuals, if Cross-Filter is set to "Both" on both ends, we can relate every column from N tables? The filter propagates through the Bridge right?
- v-danhe-msft
Hi unclejemima,
could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?
Regards,
Daniel He
