Forum Discussion
Issue with creating relationships
I have three tables and a calendar table that I am trying to create relationships for.
The first table is a sales table that has the dates, employee information and sales data.
Second table is an exceptions table that shows any time an employee was assigned a different task outside of sales. It has the dates, employee data, the exception type and time.
The third table is an HR table that has employee time off exceptions such as out sick, left early sick or vacation.
Im trying to build the relationships based on the employee and dates between all 3 tables to determine the employee sales, the sum of any exceptions or if they were out sick.
I try to build relationships on the date and the employee ID number but I'm getting errors and they make the relationships inactive.
It works when linking on the employee ID but when I go to add the date relationships it doesn't work.
Any ideas?
Based on your description, and following best practices, you should:
1) Have a date table (as you have) and join the date field with each of the date fields in your tables.
2) Create new dimension tables for fields included in the different tables (such as Employee). These dimension tables should include unique values of the field you are going to use to create the relationship (they can contain more than one column, but the field you use to establish the relationship must be unique values), and create a one-to-many (or one-to-one) relationship between the dimension table(s) and the corresponding fields in the fact tables.
You then use the dimension table fields in your slicers, filters, measures etc...You can have more than one relationship between a dimension table and your fact tables, but only one of these will be active: this should be the field you will be normally filtering (you can activate "inactive" relationships using the function USERELATIONSHIP).
3 Replies
hi Turf03 - Power BI doest allow to create relationship with multiple fields between tables - you best approach would probably be to create a concatenated column of Employee and Date in each of the tables and join based on the calculated field.
It would be helpful to understand the problem a little better if you can share a sample Power BI file
Please mark the above comment as a solution to help others find it more quickly. Also please provide a 👍 if my comment helped with solving your issue. Thanks!
You should create a "master" table that only has one unique entry for each individual employee ID. You may also do it with the time off exception assuming there are different types of exceptions that an employee could use multiple times.
For the employee ID one, go into power query and duplicate a table that has at least one of every employee ID (your sales table, I would think). Remove all columns except for employee ID, then remove the duplicate rows. Now you can create relationships between that table and your other tables that have employee ID as a field.
Then just do the same thing with others if you need/want to.
Based on your description, and following best practices, you should:
1) Have a date table (as you have) and join the date field with each of the date fields in your tables.
2) Create new dimension tables for fields included in the different tables (such as Employee). These dimension tables should include unique values of the field you are going to use to create the relationship (they can contain more than one column, but the field you use to establish the relationship must be unique values), and create a one-to-many (or one-to-one) relationship between the dimension table(s) and the corresponding fields in the fact tables.
You then use the dimension table fields in your slicers, filters, measures etc...You can have more than one relationship between a dimension table and your fact tables, but only one of these will be active: this should be the field you will be normally filtering (you can activate "inactive" relationships using the function USERELATIONSHIP).
