Can't create relationship I need

[EB09]

Hi all, 

I'm relatively new to Power BI, I am stuck with something I'm trying to do & wonder if anyone can help or has any suggestions.

Essentially I have a range of tables (imported from excel files in sharepoint) but am concentrating on two in particular. Employee demographics and Sick leave. 

My demographics table contains a range of columns around employee data showing things such as what country they are based in, person ID (employee ID), whether they're active/inactive etc. 

My sick leave table shows individual rows of data from the HR system where people have taken sick days, this data also includes the person ID albeit in a different column name. 

Now, as I want to create formula around sick leave % by things such as headcounts in different countries I want to create a relationship between them. 

What is the best way to do this? The only relationship it allows is many to many. I've made sure the columns (employee IDs) are the same data type. 

Is it not allowing a different relationships because my Sick Leave table has duplicates of the same employee ID in the same column? (a record is created each time someone has a period off sick)

I'm not sure what the best way to do this is, especially as the individual records are valuable to my dashboard as well as it shows the quantity of sickness spells.

Any help would be great & hope what I have explained makes sense!

[v-yanjiang-msft · Accepted solution]

Hi @EB09 ,

According to your description,  you are trying to create a relationship between your Employee Demographics table and your Sick Leave table in Power BI. If the only relationship it allows is many-to-many, it may be because there are duplicates of the same employee ID in the Sick Leave table.

One possible solution could be to create a new table that combines the unique employee IDs from both tables. You could do this by using the "Append Queries" function in Power Query Editor. Once you have this new table, you can create a one-to-many relationship between it and both the Employee Demographics table and the Sick Leave table. Alternatively, you could try creating a calculated column in your Sick Leave table that uses the RELATED function to pull in the country information from the Employee Demographics table based on the employee ID. This would allow you to create a relationship between the two tables based on the employee ID column.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[liberodaniele2]

Did you check if there are some duplicates in the employee table?