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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. 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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.