Forum Discussion
One Key, two tables relationship
I have one table named "key employees" with employee names. I have two one-to-many relationships from the employee table (tblLeaveMaster and Masterall). My problem is that my Masterall table has a "active?" column that I need to use to filter my tblLeaveMaster. I basically want to filter out non-active employees.
I think that all I need is a calculated column in my tblLeaveMaster that checks the [employee name] against masterall[employee name] and then sees if masterall[active?] has a 1. It seems like the relationship is there in the view, but doesn't actually work in the visuals. Any help is appreciated.
Hi joshcomputer1,
Since you have established one to many relationship for both 'tblLeaveMaster' and 'Masterall', there existing a indirect many to many relationship between them. It is not possible to add fields from these two tables in a single visual because it can't determine the mapping relationship.
To check the [employee name] in 'tblLeaveMaster' against MasterAll[employee name], please create a calculated column in 'Key Employee' table.
Active = LOOKUPVALUE(MasterAll[Active],MasterAll[Employee],'Key Employee'[Employee])
Then, you can drag 'tblLeaveMaster'[Employee Name] and 'Key Employee'[Active] into the same table visual.
Best regards,
Yuliana Gu
2 Replies
Hi joshcomputer1,
Since you have established one to many relationship for both 'tblLeaveMaster' and 'Masterall', there existing a indirect many to many relationship between them. It is not possible to add fields from these two tables in a single visual because it can't determine the mapping relationship.
To check the [employee name] in 'tblLeaveMaster' against MasterAll[employee name], please create a calculated column in 'Key Employee' table.
Active = LOOKUPVALUE(MasterAll[Active],MasterAll[Employee],'Key Employee'[Employee])
Then, you can drag 'tblLeaveMaster'[Employee Name] and 'Key Employee'[Active] into the same table visual.
Best regards,
Yuliana Gu
Not sure if this is best practice, but I merged the employee key with masterall. This gets me the list of active employees with their most recent stint on a team. I replaced the employee key with the merge.
