Help with USERELATIONSHIP on 2 inactive relationships

[Anonymous]

Can someone clarify my thinking on how USERELATIONSHIP will handle this scenario...

Looking at the image below, I have 4 tables (part of a much more complex model), linked with many relationships for different use-cases. I need to do some formulas which require me to go from the Employee table, through the Job Validation Details table on to the KOP Source table to get the right results for my measure. 

As I have 2 inactive realtionships either side of the Job Validation table, my thinking is that if I do 

CALCULATE(my formula,USERELATIONSHIP(Employees[Employee Number],'Job Validation Details'[Employee Number])) it will then force the relationship between Job Validation Details and KOP Source to also become active - is that correct? If not, how do I force both relationships to be active?

[MFelix · Accepted solution]

Hi @Anonymous,

That is correct USERRELATIONSHIP only enables the indicated relationship for the duration of the calculation, so if you are making for example a count function when you add the USERRELATIONSHIP between employees table and job validation you will get the number of employes that are related in both tables.

Check the links below with further insights:

https://docs.microsoft.com/en-us/dax/userelationship-function-dax

https://www.sqlbi.com/blog/marco/2013/10/01/using-userelationship-function-in-measures-and-calculated-columns-dax-powerpivot-ssas/

Regards,

MFelix