cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ivyhai
Post Patron
Post Patron

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.






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.





@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






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

@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?

View solution in original post

@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.

@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






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 @ivyhai






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






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.

 

 

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

dkay84_PowerBI
Microsoft
Microsoft

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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.