Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

USERELATIONSHIP to get text column through an inactive relationship

Hello,

 

I have two relationships between two tables, one of them is active and the other one is not:

 

The active relationship is between DimPeople's IDPeople and FactPeople's IDPeople, and the inactive is between DimPeople's IDPeople and FactPeople's IDManager. I want to create a measure that returns the LastName (String) from DimPeople depending on the IDManager and not on IDPeople.  I have tried:

ManagerFirstName =
CALCULATE (
    MAX ( 'core DimPeople'[LastName] ),
    USERELATIONSHIP ( 'core DimPeople'[IDPeople], 'hr FactPeople'[IDManager] )
)

 

But it returns the Name of FactPeople's IDPeople. I have also tried adding an extra filter using CROSSFILTER(IDPeople,IDPeople,None) in order to deactivate the active relationship, but it returns the same thing as the expresion above.

 

I have tried LOOKUPVALUE like this

ManagerLastName =
LOOKUPVALUE (
    'core DimPeople'[LastName],
    'core DimPeople'[IDPeople], MAX ( 'hr FactPeople'[IDManager] )
)

 

and the result is what I need. However, I want to find a solution with USERELATIONSHIP since the LOOKUPVALUE solution takes way too long to update the content of the visualizations.

 

Thank you in advance!

No Replies