Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Anonymous
Not applicable

Creating relationships / Creating a table with the same values from 2 tables.

Hello,

 

I am trying to create relationships between 2 tables. 
Table 1- contain actual list of all employees.

Table 2- contain list of trainings that employees has done in a past- contain all trainings done by employees (there are less trainings done than people on a actual employee list, some employees done 0 trainings, some done 1 or more- they have more than 1 row in a training table)

 

When trying to connect both, this is what happens:

TomaszB_0-1612348031588.png

(imię i nazwisko = first and last name)

I did connect in the past multiple tables, know more or less how the cardinality works.

 

After thinking about it, came to conclusion that maybe it is happening because:
In a list of trainings, there are people who does not work anymore in the workplace and they do not appear on the actual list of employees- so I want to indentify those people from [trainings] table, and create new one without them.

 

Need to:
-compare 2 tables, and create new [training table] with people that appear on both tables (first name and last name)
-or indentify employees who does not work anymore and delete them from the list

Have beeen thinking about functions like EXCEPT, INTERSECT but they wont work by themself 😕

 

Please correct me if I am thinking wrong

1 ACCEPTED SOLUTION

Hi Tomas,

This kinda depends on what the cause of the issue is. If these 4 employees are actually 1 employee, the best course of action would be to delete the 3 duplicate records (preferably in the data source, but you could do this in Power query as well with the remove duplicates option) because this would be a data quality issue that could cause trouble in the future as well. If these are 4 unique employees which happen to have the same name, you would have to find a different way to formulate the employee key (for example by a employeeID if the this is available in the source). But I assume the issue is duplicates in this case, since all the doubles have the same name. 

I hope that clarifies!

 

regards,

 

Tim





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

4 REPLIES 4
timg
Solution Sage
Solution Sage

Hi TomaszB,

I think the Many to many issue source can rather be found in the employee table. it is quite normal to have a table such as 'trainings' where one employeeID is referenced multiple times. However, the many-to-many pop-up shows up when your dimension table (in this case your employee table) contains duplicates as well. So in this case it looks like one unique employee ID is occurring multiple times in your employee table (in other words it is not a unique id atm). Could you verify whether the employee table has unique values (e.g. by creating a table visual with the employeeID and COUNT(Employeeid) from this table)?

 

regards,

 

Tim 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

Thanks for your quick reply!

 

You are right:
After creating a measure:

TomaszB_1-1612351804883.png

It turns out that there are 4 employees with the same first and last name.

 

Do you have an idea how to resolve the issue now?

I can create uniqe ID for every employee in the Actual employee table by using e.g. index column , however- then how to assign those unique IDs to the people in training table?

Hi Tomas,

This kinda depends on what the cause of the issue is. If these 4 employees are actually 1 employee, the best course of action would be to delete the 3 duplicate records (preferably in the data source, but you could do this in Power query as well with the remove duplicates option) because this would be a data quality issue that could cause trouble in the future as well. If these are 4 unique employees which happen to have the same name, you would have to find a different way to formulate the employee key (for example by a employeeID if the this is available in the source). But I assume the issue is duplicates in this case, since all the doubles have the same name. 

I hope that clarifies!

 

regards,

 

Tim





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

Thank you! 

 

I did check my datasource- there were duplicates that should not be there.

It will help to prevent future problems with the reports 🙂

 

However there is 1 employee working on 2 positions, so giving an employee ID might be neccessary.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.