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
Anonymous
Not applicable

Create Relationship

I'm tring to create the relationship between two "description", i'm 100% sure the 'description' under "masterCategory-OfficeTools' is unique.  

plus why 'description' and 'Category' had Sum symbol at left? 

CRM.PNG

1 ACCEPTED SOLUTION

In the query editor there is a remove duplicates function when you import your data from excel.  Did you ever try using that?

 

Also in excel are you checking for dups on just the one column or the two that look highlighted?

View solution in original post

19 REPLIES 19
parry2k
Super User
Super User

As message says, seems like your both table have duplicate values, one has to be unique.

 

Basically you cannot have many to many relationship, check your related to table if that contains duplicate values.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

@parry2kYes, i know that.  1 table definitly have no duplicate, I've tried many ways, even delete the bottom rows to make sure no any blank values.  but still give me the same message.  i've done similar things, but don't have that problem before.   any other possiblity might caursing that error? thanks. 

CRM.PNG

1. In the query editor, reference your fact table[column]

2. Convert to table

3. Trim and Clean this column

4. Remove dups

5. Close and Apply

6. Create relationship from your fact table to this dimension table and from this dimension table to your other table

 

See if this allows you to build the relationship

@dkay84_PowerBI he already confirmed there are no duplicates



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

 Also, I am asking him to create an intermediate table between his original fact and dimension tables

@parry2k I've had situations in the past where unprintable characters in a record(s) were creating this problem of not being able to create a relationship

Anonymous
Not applicable

@dkay84_PowerBI @parry2k @Baskar  thanks, i think i've found the problem, it's the duplicates.  i've created a chart below in power bi to find the duplicates for me.  Yes, there are 1 value in twice.  but still excel still tell me there is NO duplicates. Dam. 

 

thanks all. 

CRM.PNG

CRM.PNG

 

 

 

Even i have seen query editor works fine in deleting any dublicates.

In the query editor there is a remove duplicates function when you import your data from excel.  Did you ever try using that?

 

Also in excel are you checking for dups on just the one column or the two that look highlighted?

Anonymous
Not applicable

@dkay84_PowerBI no. i alway remove duplicates from excel and it's normally work. but will try in the future. thanks. 

Sometimes I run into trouble when importing excel data (non-printable characters) for whatever reason.  I always do a trim and clean to remove any inadvertant characters or whitespace that may be in the cell's value in order for PBI to read it correctly.

Anonymous
Not applicable

@dkay84_PowerBIhow you do the clean in power bi?  could you please describe a bit detail?  many thanks. 

In the query editor there is a built in function (under Transform tab I believe). Not sure if there is a DAX equivalent although there is a CLEAN function in excel.

wow, good to know it is resolved.

 

Cheers,

P



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

That make sense, thanks for sharing your experience. I hope it resolve the issue for @Anonymous



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Did you looked at Cross Filter Direction on relationship window? Not sure if that has anything to do with this but check if it is single



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Baskar
Resident Rockstar
Resident Rockstar

Cool Dude,

 

 

Change the Description Data Type to Text and Try.

 

 

Anonymous
Not applicable

@Baskar i've changed both 'description' to text, but still give me same error message.  

The symbol next to the fields means they are numeric data type with a default aggregation.

If the MasterExport table is your fact table, try creating a new query by referencing it (right click "reference") and then add [Description] to the source line in the advanced editor or formula bar. Then trim and clean and then remove dups. Then try linking both original tables to this lookup table. You may have to change crossfiltering direction to both depending on how you want to work with the data.

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.