Get selected value USERELATIONSHIP (no aggregation)

[joris-kempen]

I have a simple model:
dim Employee  -->  employee_id + Name

fact Complaints --> complaint_id + registror_id  + executor_id + duration.   

Registor ID is the Employee who made the complaint

Executor ID is the employee to solve the complaint.

My idea this can be done by make an active relationship between employeer_id and registor_id to show the Registors (put dim Employee [Name] in a table column.  Also make an inactive relationship between employee_id and executor_id:

And I wrote a measure to show the Executor:

Executor Name =

    CALCULATE(

        SELECTEDVALUE('dim Employee'[Name]),

        USERELATIONSHIP('dim Employee'[employee_id],'fact Complaints'[executor_id])

    )

When i put Complaint ID + Measure Executor on a table, i just get an empty table.

When i put on complaint ID + dim employee [Name] + Measure Executor i get this repeated:

This is my fact table:

And so i wanted to get a table like:
ID  Registror    Executor
1   John Doo | Marge Peterson
2  John Doo | John Doo

etc
Can this be done with the CALCULATE / SELECTEDVALUE / USERELATIONSHIP?

Or do i need to use RELATED / LOOKUP?

- i rather not have:

- make multiple copies of the dim Employee table

- i dont want to make extra columns in the fact Complaints that contain the full name

========================

EDIT

- I have been thinking some more: if i want to filter in a slicer using dim Employee [Name]  this never would  be possible for the inactive relationship?  As i can't put a Measure is a Slicer.
- so best practice would be to make multiple dim Employee tables? (Just reference in the Power Query)

[joris-kempen · Accepted solution]

I found a link which discusses this:
https://community.fabric.microsoft.com/t5/Desktop/Dealing-with-multiple-references-to-the-same-table...

 simply the model and create a proper star schema it would be recommended to have two user tables, one will be Sales Owner Users and the other Relationship Owner Users. There really isn't a need to create complexity in measures / visuals / report with a single user table (which usually only have a few hunderd or a few thousand records)

So it would be best practice to make multiple references! I probably was overthinking this 🙂

[Anonymous]

Hi @joris-kempen ,
Glad to see that you found the documentation to solve your problem, it will help other users on the community who have the same problem!

Best regards,
Albert He