Forum Discussion

joshcomputer1's avatar
joshcomputer1
Helper V
8 years ago
Solved

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...
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    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

v-yulgu-msft's avatar
v-yulgu-msft
Microsoft Employee
8 years ago

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's avatar
    joshcomputer1
    Helper V
    8 years ago

    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.