how to connect 1 table to multiple tables to get employee detail information

[tomperro]

I have two tables that I need to create two seperate relationships between and I am not sure how to accomplish this.

I have a Contact Table with a list of employees and the type of employee they are.

The other table is an Observtion Table that includes observations that a supervisor has done on an employee.

I need to relate these tables 2 ways.

1. Observation.Employee_ID to Contact.Employee_ID - to see what observations have been done on the employee.

2. Observation.Supervisor_ID to Contact.Employee_ID - to see what observations a supervisor has completed.

My workaround was to create two contact tables, one for the employee and one for the supervisor but this seems excessive.

Observation Table

Employee_ID	Observation_ID	Supervisor_ID
1111	ABC	9999
2222	DEF	8888
3333	GHI	7777

Contact Table

Employee_ID	Employee_Type
1111	Employee
2222	Employee
3333	Employee
9999	Supervisor
8888	Supervisor
7777	Supervisor

[v-zhouwen-msft · Accepted solution]

Hi @AspiringAnalyst ,thanks for the quick reply, I'll add further.

Hi @tomperro ,

Regarding your question, perhaps you could use the function 'USERELATIONSHIP'

USERELATIONSHIP function (DAX) - DAX | Microsoft Learn

The Table data is shown below:

Please follow these steps:

1.Creating table-to-table relationships

2. Use the following DAX expression to create a measure

MEASURE =
VAR _a =
    SELECTEDVALUE ( 'Contact Table'[Employee_Type] )
VAR _b =
    IF (
        _a = "Supervisor",
        CALCULATE (
            CONCATENATEX ( 'Observation Table', [Observation_ID], "," ),
            USERELATIONSHIP ( 'Observation Table'[Supervisor_ID], 'Contact Table'[Employee_ID] )
        ),
        CONCATENATEX ( 'Observation Table', [Observation_ID], "," )
    )
RETURN
    _b

3.Final output

Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tomperro]

If I do this would I have to include userelationship in each measure or calculated column that I create?

[AspiringAnalyst]

I'd think that the single connection between Employee_ID with the cardinality set to both ways would suffice.  If you set a slicer to supervisor IDs, then you'd be able to get the list of observations and their associated employees.

You need two tables.  One is the fact table of employee information and the other being the list of observations.

[tomperro]

The problem is that in my observation table there is an employee id (Observation.Employee_ID) and a supervisor id (Observation.Supervisor_ID).

The contact table has contact information.

I need to make a connection from the Observation.Employee_ID to the Contact.ID but also need a connection between the Observation.Supervisor_ID to the Contact.ID.