Row Level Security using AD-ENT group

[ldwf]

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!

[v-henryk-mstf · Accepted solution]

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]

---

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

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?

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.

[ldwf]

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