One Key, two tables relationship

[joshcomputer1]

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. 

[v-yulgu-msft · Accepted solution]

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

[joshcomputer1]

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.