Power BI Table Relationship

Question

I am trying to create two relationships between tables in Power BI. I have a table (Observations) that contains an employee_id and a supervisor_id that I am trying to connect to my Contact table. I need to look up the employee information in the Contact table by using employee_id in the Observation table and also look up the supervisor information in the Contact table using the supervisor_id in the Observation table.

              Contact Table                                  Observation Table
           ===================                               =================
Employee_ID     Name           Position             Observation_ID    Employee_ID    Supervisor_ID
---------------------------------------             ----------------------------------------------
123             John Doe       employee             1                123             321
321             Harvey Sample  supervisor           2                456             654
456             Mickey Mouse   employee             3                456             654
654             Donald Duck    supervisor           4                123             321

Answer 1

One possible way is to create two relationships between the observation and contact table:

1. Create a relationship between Observation Table(Employee_ID) and Contact Table(Employee_ID). The Cardinality should be many to one, and set this relationship as active.

2. Create a relationship between Observation Table(Supervisor_ID) and Contact Table(Employee_ID). The Cardinality should be many to one, and set this relationship as inactive.

create an inactive relationship Power BI

You will then need to use the DAX USERELATIONSHIP function when you want to use the inactive relationship in a formula. Here is a detailed explanation of how to use USERELATIONSHIP: https://www.sqlbi.com/articles/userelationship-in-calculated-columns/

Alternatively, you can duplicate the contact table and use one copy for the Employee_ID relationship and the other for the Supervisor_ID relationship. Which option is best will depend on your use case.