RLS - At a group level

[Pat_learner]

Hi,

I would like to set up RLS at a team level since each team have access to different forms.  I thought the problem I'm trying to solve is pretty simple.  I have 2 tables. 

   Table#1-'Rep-Product' - fields include (ProductId, ServiceName)

   Table#1-'Team-Service' - fields include (TeamName, ProductId).  This is a table listing which team have access to which product Id

Products                                                                          Team-Service

ProductId          ServiceName                                         Team           ProductId

A                       Service_A                                               Team One             A

B                        Service_B                                               Team One             B

C                       Service_B                                               Team Two              C

What I want to do is to set up RLS such that when a member of team One logs on to the report, he will see Product A, B only and Team Two will see only product C.  In my report the Product table is linked to other tables.  I am using the product table to slice informatoin on the report based on who is logging in at a team level.

RLS setup

I started by

1. create the first role called 'Team One', 

2. set a filter on the 'Products' table with the following DAX

    'REP-Products'[ProductId]=LOOKUPVALUE('Team-Services'[ProductId],'Team-Services'[Team],"Team One")

3. I didn't set up any user security table.  I figured after I add a user to 'Team One' role, when that user has logged in, the system will detect that the user is 'Team One' and the DAX expression above will execute.  This obviously is a false assumption because the report is totally empty when I tested it by 'View As Role' option.   Do I need to create the Team-Users table listing which users are on which team ?  Then what does the 'adding user to a role' function of Power BI do.

My question is

1. what did I do wrong with the set up.

2. Is it possible to just set up DAX filter on the 'Team One' role to just list valid forms for 'Team One'.

     'REP-Products'[ProductId]= (A,B)

   

Any help is greatly appreciated.  Thanks.

Pat

[v-sihou-msft · Accepted solution]

@Pat_learner

1. what did I do wrong with the set up.

If you need to detect team belonging for all access users, it's better to have a Team-User Table. Otherwise you need to create one role for each Team.

2. Is it possible to just set up DAX filter on the 'Team One' role to just list valid forms for 'Team One'. 'REP-Products'[ProductId]= (A,B)

You can directly use row level filter expression like:

'REP-Products'[ProductId]= A || 'REP-Products'[ProductId]= B

Rergards,