cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ldwf
Helper II
Helper II

Row Level Security using AD-ENT group

Hello, I have a PBI model and want to do Row Level Security.  I don't want to do this creating roles; instead I am hoping this can be done using AD groups.  We currently have one AD group that people get added to when they want to view our dashboards.  I would like to create an additional AD group which would restrict the records people in this new AD group have access to.  I would like to create a simple table of two columns - 1. the AD Group, and 2. An Indicator.  The indicator is the field I would use to determine what the AD Group has access to; it would have values of 0 or 1.  I would add this table to the model and join it to the fact table by the Indicator.  When a person views a dashboard, they would then either be restricted from seeing some records or be able to see everything based on the indicator and what AD Group they are in.   Is this possible?  I've been playing around with AD tables but I haven't seen any field values that match with our AD Group names.  Thanks!

1 ACCEPTED SOLUTION
v-henryk-mstf
Community Support
Community Support

Hi @ldwf ,

 

Whether the advice given by @d_gosbell  has solved your confusion, if the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.


Looking forward to your feedback.


Best Regards,
Henry

View solution in original post

4 REPLIES 4
v-henryk-mstf
Community Support
Community Support

Hi @ldwf ,

 

Whether the advice given by @d_gosbell  has solved your confusion, if the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.


Looking forward to your feedback.


Best Regards,
Henry

d_gosbell
Super User
Super User


@ldwf wrote:

I don't want to do this creating roles; instead I am hoping this can be done using AD groups.  


You cannot create RLS rules without a role. The role is the object that maps the filter rules to AD accounts or groups.

 


@ldwf wrote:

I would like to create a simple table of two columns - 1. the AD Group, and 2. An Indicator.  The indicator is the field I would use to determine what the AD Group has access to; it would have values of 0 or 1. 


So this pattern is called "dynamic RLS" and what you want to do is not possible as there is currently no way of getting group membership information using DAX. You can add an AD group to the role membership to determine who gets the table level filtering applied, but the actual table would have to have Username and Indicator columns as you can only get the Username() or UserPrincipalName() via DAX.

v-henryk-mstf
Community Support
Community Support

Hi @ldwf ,

 

The group that sets rls must meet the following conditions, and in addition what are the types of members in your group respectively?

vhenrykmstf_0-1662689135743.png

related document:

Row-level security (RLS) with Power BI - Power BI | Microsoft Docs


If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.


Best Regards,
Henry


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks but I am looking for a solution that doesn't involve setting up roles.  I'm looking for a solution based on the AD Group the user is in. I am able to query AD but there are so many fields I don't see what field is the actual AD Group.  So if a user is in AD Group ABC, my Excel spreadsheet would indicate that Group ABC has an indicator value of 1, which means they have access to rows where the indicator value in the fact table is 1.  This way, I create a spreadsheet just one time containing the two AD Groups and the indicator column.  I incorporate this spreadsheet into the model and it's done.  it is based totally on the Active Directory group.  Thanks 

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!