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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Chanise89
Helper I
Helper I

USERELATIONSHIP function to return text

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)

Chanise89_0-1713995281757.png

 

the table on the right is what it should be

Chanise89_1-1713995377612.png

 

Any help will be greatly appreciated! 

 

1 REPLY 1
amitchandak
Super User
Super User

@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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.