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
kfschaefer
Helper IV
Helper IV

Can't Create Relationships - missing intermediate data to connect the two columns

3.PNGI am attempting to create a relationship between the MainData Table and GroupDetail Table via the GroupNumber & Group Name.  However I am getting the following issue:

 

Why?

 

Thanks!

 

6 REPLIES 6
varakcheev
Employee
Employee

PowerBIDesktop shows this message when it detects a Many-Many relationship (which are not supported). Basically at least one of the columns must have unique non null values.


Please doublecheck that the GroupDetails[GroupName] column doesn't have duplicates or nulls. (BTW comparison is case insensitive so "GROUP 1" and "group 1" are considered a dupe). If you find a dupe/null you can filter it out in the "Edit Query" view and retry to create the relationship.

mork
Helper V
Helper V

Usually this happens because one of your columns doesnt have all the data that the other has. Another requirement of the relationship function though is to not have any blanks in either of the two columns that you are trying to relate and also only one of the two columns is allowed to have the same value multiple times. One of the two columns always has to have each distinct value once.

Sean
Community Champion
Community Champion

@kfschaefer You are missing a [GroupName] in the Table that contains the unique values of GroupNames which looks to be GroupDetail table.

 

Meaning in the table on the MANY side you have at least one [GroupName] that does not exist in the table on the ONE side.

 

MainData table                  GroupDetail table

GroupName Column          GroupName Column

GroupName 1                     GroupName 2

GroupName 1                     GroupName 3

GroupName 2

GroupName 3

GroupName 3

 

 

 

Try to copy (duplicate) GroupDetail table, then remove all columns from this table except GroupName. Then remove duplicates. This new table that will contain all unique values for GroupName can be used as your intermediate table to connect GroupDetail and MainData tables. Hope this helps.

In this case, maybe it will be better to use MainTable since it contains all GroupNames....

@kfschaefer Maybe I'm not understanding something... The relationship would be on Group ID in both tables... Is this the case, and your description makes it sound like you are trying to create a relationship from name to ID...


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

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.