Forum Discussion
Creating a 1-Many Relationship
Hi everyone,
I have a master table with an identifier which is the company name. I know that in my data model there will be multiple tables, so I would need to establish a realtionship. That said, in the master table, there are duplicates in the company name. As such, any relationship I create will be a Many-Many relationship, which I do not want.
As a workaround, I created a new table with just the company name and removed duplicates. However, when I tried to create a relationship between the 2 company name columns, the only option it allows me is to create a Many-Many relationship.
Anybody know how I can get around to troubleshooting this?
HI rjsidek ,
You can consider using union, distinct, values functions to create a calculated table to extract and combine distinct value from original tables and use it as a bridge table to link the table with 'one to many' relationships.
Bridge = DISTINCT ( UNION ( VALUES ( Table1[Column1] ), VALUES ( Table2[Column1] ) ) )If above not work, can you please share some dummy data for test?
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
4 Replies
First make sure that there are no duplicates in your distinct table:
Select the data-view:
Select the correct table and column, at the bottom left of the screen the following information should be displayed:
As you can se here my lndex column that is selected has the same amount of distinct values as the total number of rows in the table. This means that the column is completly unique.
Check this and then get back to me! 🙂
Br,
JHi tex628
I checked it, and you were right! It seems that I have 438 rows but only 436 distinct values. I tried to remove dupicates in power query but it is still giving me the same result. Managed to find one of the duplicates, but I cannot for the life of me find the other one. Is there a way to do like conditional formatting to highlight duplicates in powerbi?
HI rjsidek ,
You can consider using union, distinct, values functions to create a calculated table to extract and combine distinct value from original tables and use it as a bridge table to link the table with 'one to many' relationships.
Bridge = DISTINCT ( UNION ( VALUES ( Table1[Column1] ), VALUES ( Table2[Column1] ) ) )If above not work, can you please share some dummy data for test?
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
This did the trick, thank you! but I still find it weird cause I removed duplicates but still did not have all distinct values, which is weird
