Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
tomperro
Helper III
Helper III

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

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_IDObservation_IDSupervisor_ID
1111ABC9999
2222DEF8888
3333GHI7777

 

Contact Table

Employee_IDEmployee_Type
1111Employee
2222Employee
3333Employee
9999Supervisor
8888Supervisor
7777Supervisor
1 ACCEPTED SOLUTION
v-zhouwen-msft
Community Support
Community Support

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:

vzhouwenmsft_0-1715061321566.png

vzhouwenmsft_1-1715061332670.png

Please follow these steps:

1.Creating table-to-table relationships

vzhouwenmsft_2-1715061396197.png

 

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

vzhouwenmsft_3-1715061503962.png

vzhouwenmsft_4-1715061521457.png

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.

 

View solution in original post

4 REPLIES 4
v-zhouwen-msft
Community Support
Community Support

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:

vzhouwenmsft_0-1715061321566.png

vzhouwenmsft_1-1715061332670.png

Please follow these steps:

1.Creating table-to-table relationships

vzhouwenmsft_2-1715061396197.png

 

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

vzhouwenmsft_3-1715061503962.png

vzhouwenmsft_4-1715061521457.png

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.

 

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

AspiringAnalyst
Frequent Visitor

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.

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.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.