USERELATIONSHIP function to return text

[Chanise89]

Hi All, 

I hope someone can help me with this.

I have two tables:

Table 1: Is the Hierarchy levels for our Centers (For my report I only need Level 5 and Level 6) 

Table 2: is the Center and the owners

In Power Query, I did merged the two tables together to create new columns for 'Level 5 owners" and "Level 6 owners" 

Then I made a relationship between the Owner table and and the newly merged table. 

My issue is:

I have to use a Matrix table because we need the users to drill down from Level 5 to Level and see the owners at each level as well. Which means I would need the owners for both levels to be in one Column/Field. 

I tried using this measure - which kind of works but using Min or Max doesn't give me the correct owner (sometimes)

the table on the right is what it should be

Any help will be greatly appreciated! 

[amitchandak]

@Chanise89 , I think you need switch measure based on level, using isinscope

Switch(True(),
isinscope(OnwerTable[Level 6]), CALCULATE(Min(OnwerTable[VCHNAME]),USERELATIONSHIP(OnwerTable[VCHNAME],Merge[Level 6 Owner])),
isinscope(OnwerTable[Level 5]), CALCULATE(Min(OnwerTable[VCHNAME]),USERELATIONSHIP(OnwerTable[VCHNAME],Merge[Level 5 Owner])))

How to Switch Subtotal and Grand Total in Power BI | Power BI Tutorials| isinscope: https://youtu.be/smhIPw3OkKA