Role Filter - Link user to other users Teams

[CraigDawson]

I have a Power BI for Managers to look at the stats of their agents. We also want to assign trainers to each of the teams and make it so that the trainers can only see the data of the teams they are assigned to. We might have multiple trainers assigned to a single team, and a single trainer can be assigned to multiple teams, so I have a many to many relationship table to manage which trainers are assigned to which teams via their emails. The table in this formula is called 'Data: Barcelona Coach Relationships'.

I can get the filter to work if I only use one relationship:

AND(
[Location] = "Barcelona",
OR(
[Agents Manager Email] = USERPRINCIPALNAME(),
[Agents Manager Email] =
LOOKUPVALUE(
'Data: BarcelonaCoach Relationships'[ManagerEmail],
'Data: BarcelonaCoach Relationships'[CoachEmail],
USERPRINCIPALNAME()
)
)
)

However this will only find the first team they are assigned to so seems to throw up an error if multiple teams are assigned. I'm trying to get it to work using "in" instead of "=" and using "FILTER" instead of "LOOKUPVALUE".

AND(
[Location] = "Barcelona",
OR(
[Agents Manager Email] = USERPRINCIPALNAME(),
[Agents Manager Email] in
FILTER(
'Data: Barcelona Coach Relationships',
'Data: Barcelona Coach Relationships'[CoachEmail] =
USERPRINCIPALNAME()
)
)
)

This formula is throwing the error "The function expects a table expression for argument '2', but a string or numeric expression was used." so I'm assuming I've done something wrong with the FILTER. Can anyone tell what's wrong?

[OwenAuger · Accepted solution]

Hi @CraigDawson 

I would suggest writing it like this:

AND (
    [Location] = "Barcelona",
    OR (
        [Agents Manager Email]
            = USERPRINCIPALNAME (),
        [Agents Manager Email]
            IN CALCULATETABLE (
                VALUES ( 'Data: Barcelona Coach Relationships'[ManagerEmail] ),
                'Data: Barcelona Coach Relationships'[CoachEmail]
                    = USERPRINCIPALNAME (),
                REMOVEFILTERS () -- safeguard
            )
    )
)

The REMOVEFILTERS () may be unnecessary and could be removed if 'Data: Barcelona Coach Relationships' is unrelated to any other tables (i.e. context transition in the RLS table won't affect it).

Does this work for you?

Regards

[CraigDawson]

Thanks for the quick response, yes that one works! I also found that the error was because the filter was returning a table with multiple columns. If I changed it to this it also works but I believe your formula adds an extra layer of failsafe.

AND(
[Location] = "Barcelona",
OR(
[Agents Manager Email] = USERPRINCIPALNAME(),
[Agents Manager Email] in
SUMMARIZE(

FILTER(
'Data: Barcelona Coach Relationships',
'Data: Barcelona Coach Relationships'[CoachEmail] =
USERPRINCIPALNAME()
),'Data: Barcelona Coach Relationships'[ManagerEmail])
)
)