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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Pat_learner
Frequent Visitor

RLS - At a group level

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)

   RLS_Question.gif

Any help is greatly appreciated.  Thanks.

Pat

1 ACCEPTED SOLUTION
v-sihou-msft
Employee
Employee

@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,

View solution in original post

1 REPLY 1
v-sihou-msft
Employee
Employee

@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,

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.