Problem matching 2 fields in a relationship to a staff list

[mitchreece]

I am trying to join 2 fields from one table to a list in another

i.e Table one contains a list of clients, a field for the manager, a field for the partner and other fields.

Table 2 contains a list of staff (Which the manager and partner are part of), and a team for each staff member.

I believe it's not possible to 'join' them using relationships so am looking for another way to relate them as I am using row level security and I need to do that based on team in table 2.

(I need to relate both 'Partner' and 'Manager' in [Agent Listing] to 'Name' in [Staff] so that I can run report wide filter on 'Team' in [Staff] and it will include results where either the manager or the partner for a client is in the chosen team)

[v-danhe-msft]

Hi @mitchreece,

Could you please offer me some sample data to have a test and post your desired result if possible?

Regards,

Daniel He

[mitchreece]

Hi Daniel

Thank you, I appreciate it. 

Below is a link to what the 2 tables would look like.

What I want is for the leader of Team Y (That has 'staff 2' in it) to be able to see clients C though to H as Staff 2 is either Partner or Manager on those clients.

Leader of team X (Staff 1 & 3) could see clients A through to F

Leader of team Z can see clients G & H

Hope this makes sense.

https://app.powerbi.com/view?r=eyJrIjoiMjFjMDc4NTEtMmViYS00OWFkLWE3NDktM2RmZmY0Mjc1ZGVjIiwidCI6ImRmY...

[mitchreece]

@v-danhe-msft - Did you have any luck?