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

Managing Relationships

So I have two datasets I'm attempting to merge. Both from different sources but both contain the same Company ID's highlighted in each table:

1.PNG

I'm unable to connect the two because my CService dataset contains duplicate co values because a company can have more than one service:

1.PNG

I've already tried creating a calculated table to get the distinct values, but it does not give me everything I need. I need to build some formulas but I do not know how to get around this without establishing a direct connection between these two datasets. I have retention formulas already established in my CService dataset based on the start and end dates, and I just need the account manager field from my EMS Survey dataset to add the retention formulas to see what the retention is by account manager based on the company ID my datasets in common. Any ideas??

16 REPLIES 16
Ashish_Mathur
Super User
Super User

Hi,

 

Share your soure datasets and also show your expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

@Ashish_Mathur Here are my dataset samples:

1.PNG

As you can see in Table 1, a company ID can be duplicated due to multiple services and start and end dates, so I cannot create a relationship based on that column. This is the result I'm looking to get, which I've just created manually:

1.PNG

@Anonymous can you share the data as mentioned previously? 



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

@parry2k I'm not sure how it's going to work since pieces of it is connected to our SQL DB. But here is the link:

https://drive.google.com/file/d/1hHKpiElazNqmloG6buCydspj6iUKS7mA/view?usp=sharing

 

The datasets I'm working with are 'Cservice' and 'EMS Survey' and the fields from each that I would like to connect on (if possible) are 'co' from Cservice and 'New Id' from EMS Survey.

Hi,

 

That link takes me to a page which says that i need permission.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Anonymous
Not applicable

@parry2k @Ashish_Mathur I figured that's what it would do. I am not sure how to share this data with you guys because I'm pretty sure I can't hand out company credentials.

@Anonymous first and foremost we are unable to download the file as @Ashish_Mathur .

 

Easiest way is to pull sample data from SQL in excel sheet and send that over, it will be much easier that way. You can always send private message with the link if you are not comfortable sharing on the forun. Make sure remove any sensitive information.



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

@parry2k @Ashish_Mathur  After more trial and error I believe I've figured out the solution to my own problem. I basically had to create another unique ID by merging 2 different columns. After that I was able to join the 2 tables I've been wanting to join and have been able to create the necessary formulas. Thank you both for standing by and being very responsive to my issues.

Glad to hear you found the solution, feel free to reach out anytime for another issue/help.

 

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.

ricardocamargos
Continued Contributor
Continued Contributor

Hi @Anonymous,

 

Have you tried to create a dimension to those fields which both tables have in common ?

 

Ricardo

Anonymous
Not applicable

@ricardocamargos I have not. I'm not exactly sure what that is. Would like to find out!

@Anonymous as you already create a calculated table with distinct value, I assume at that point you are able to connect both the tables with this new calculated table? Are you having trouble setting relationship? What problem we are solving here?



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

@parry2k yes, that is my exact issue. Even though I've created a table of distinct co values from 'CService', I still cannot create a table or any other visualization that has fields from 'CService' and fields from 'EMS Survey'. It only understands the relationship between 'Cservice'<->'Distinct Co' and 'EMS Survey'<->'Distinct Co' and I'm trying to get to a point where I have 'CService'<->'EMS Survey' so I can use the necessary fields from each. I thought my calcuated table of the distinct co values would bridge the gap between these two datatsets.

Can you share the sample dataset? I don't see any challenge once relationship are created. My assumption is at this point your both tables are connected to this distinct table you created, make sure your relatonship cross filter direction is set to both, which you can check on relationship dialog box.

 

If you take a simple table visual, drop fields from both these two fields, I expect you will get correct result. 

 

 



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

@parry2k Yes I can share a sample dataset. I've been asked to do this before, but I've never done so yet. How do I share with you?

Please share the file using google drive or onedrive, there is no way to share thru post.

 

 



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.

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.