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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Advocate II
Advocate II

How to leverage Active Directory to filter the data in Power BI

As you might know Power BI offers a very nice role management that allows filtering the data based on role(s) associated with the user.


A basic use of this feature is presented in


A more dynamic use of this feature is very well explained in .


But this latest option still requires to maintain a table of some sort where we associate users and their roles.

Most organizations already have associated roles to users in Active Directory and this posts aims at demonstrating how to leverage this.


It explains:

  • How to connect to AD
  • How to select the data we need in AD
  • How to transform the data in a way that makes it usable in PBI
  • How to leverage the data in PBI


How to connect to AD

This is easy because Active Directory is one the listed Data Sources

2017-03-09 15_10_34-.png

How to select the data we need in AD

After connecting to your domain, you will be presented with dozens of table. Only one of those is interesting for use here: User


2017-03-09 15_12_41-.png

How to transform the data in a way that makes it usable in PBI

What we need for every user is their email address (because it’s the user ID in PBI) and the groups they are associated with.


  • Email address

Expand the user column and look only for “mail”

2017-03-09 15_15_03-AD - Query Editor.png

  • Groups

Groups are in the distinguishedName column but it requires a bit of work to be usable

First split the column on the “,” delimiter

2017-03-09 15_17_17-.png

Then select all the distinguishedName.* columns and unnpivot them


2017-03-09 15_18_54-AD - Query Editor.png

Then split the new “Value” column on the “=” sign

2017-03-09 15_20_19-AD - Query Editor.png

If you wish you can then rename the colums. I think those are called nodes in AD so I called them

NodeID, NodeType and NodeName.


NB: if, like me, you wondered what CN, OU, DC stand for, here it is

  • CN = Common Name
  • OU = Organizational Unit
  • DC = Domain Component

2017-03-09 15_22_07-AD - Query Editor.png


Tadam you are all set!


For any user you can get the group (Nodes) they belong to

2017-03-09 15_27_46-AD - Power BI Desktop.png

And more important you can get all the users that belong to a given group


2017-03-09 15_29_03-AD - Power BI Desktop.png


How to leverage the data in PBI

Now you are simply back to the role creation issue, where you can filter the data based on the group (NodeName) of the user.


To illustrate this let’s take this financial sample workbook:


2017-03-09 15_44_25-AD - Query Editor.png


Let’s say people from the GPO group should only see data from Canada.


We need to create a table that stores all those associations.

So sure we still have a table to maintain, but this is still much more dynamic because we don’t have to maintain the group/user association. Everytime a user joins or leave a group in AD, the data get filtered accordingly.




2017-03-09 16_10_01-.png


Now we need to link this table to both the user table on the group and to the data table on the country


2017-03-09 16_16_58-AD - Power BI Desktop.png


The last piece is the association of the current user to the user.mail at run time. This is achieved by creating a role



2017-03-09 16_11_31-AD - Power BI Desktop.png

In the user table add the following table filter DAX expression:

[user.mail] = username()


2017-03-09 16_13_56-.png



The last step is to get the role to be applied at run time by applying the role to all users.


Once you application has been published in the cloud open the security menu of the dataset.



2017-03-09 17_26_32-AD-simple - Power BI.png

And add the role to all users.


2017-03-09 17_28_10-Power BI.png

The application of the role at runtime will cause

  • the username to be used to filter the data only on matching [user.mail],
  • which will in turn filter on the associated NodeName/group,
  • which will in turn filter on the authorized countries for the user.





In real life you will probably have several countries visible for a given group and several groups accessing the same countries.


2017-03-09 16_27_06-.png

In other words you will end-up with many-to-many relationships that you will need to tackle by creating relationship tables.



You can easily create those tables by referencing the existing tables


2017-03-09 16_27_51-AD - Query Editor.png

Keeping only the column you are interested in, in our case the NodeName column

2017-03-09 16_36_57-AD - Query Editor.png

Then the final step is to remove duplicates




2017-03-09 16_37_56-AD - Query Editor.png

For the relationship to work you might need to make sure there is no empty row in the list.

2017-03-09 16_41_38-AD - Query Editor.png


Please note that Power Query  and PowerBI currently don’t have the same definition of duplicates ( so be careful


 You can now do the same to get a list of all unique countries

2017-03-09 16_37_56-AD - Query Editor.png


Which will lead you to this new data model


2017-03-09 16_47_13-AD - Power BI Desktop.png2017-03-09 17_03_38-AD - Power BI Desktop.png


But for the relationships to work as expected you will need to edit the relationships 2017-03-09 17_06_33-.png


And set the Cross fitler direction to Both

2017-03-09 17_05_57-.png


Advocate III
Advocate III

That's a very interesting topic! My solution doesn't want to work though:

in the user table I have e.g. email with the country and in the role I say email = username()

When I run the view as other user "" it doesn't filter the country.

User n:1 Country 1:n Facts Table


Should that work?


Thanks, BR Roman

Not applicable



Thanks for the article, but I have few doubts.

Why should I add users to the roles/groups tab individually? I should be able to put the name of the group directly so that I save the hassle of adding the AD group members one-by-one.


Can you please help



Hi there

You need to have the user in the actual dataset tables to correctly know which user should have which RLS applied.

The groups get enumerated automatically by the Power BI Service.

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Power BI Blog

Not applicable

Hi @GilbertQ ,


I have that mapping for user to group to check that. But where I am getting stuck is when I am trying to get the AD group name populated in the service, they are not showing up. 

The only column that's missing from my data is the group mail id. Is it the reason that I am not able to see the group names?



Hi there

It would need to be an Active Directory or Azure Active Directory Security Group to see it.

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Power BI Blog

Helper V
Helper V

Thank you so much for this guide. However, I am still really new to Active Directory (like 1 day old) and I am still learning it. I was wondering, how do you establish dynamic row level security for the groups from here?


Not applicable

Hi there,


Love the idea of using the AD for permissions. I have a question though.


If a user changes group or region for an example. How easy would it be to update this solution to reflect that change and show the user the right data. Would the process have to be done all over since AD isn't a source that has data refresh.


Cheers. 🙂

Microsoft Employee
Microsoft Employee

Hi @osinquinvdm,

Thanks for your sharing.

Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.