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

[Anonymous]

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:

(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

[timg]

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 

[Anonymous]

Thanks for your quick reply!

You are right:
After creating a measure:

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?

[timg · 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

[Anonymous]

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.