DAX query - Access to read AD Security Group members

[ashishpbi]

Hello,

Is there a way to read Active Directory security group members in DAX (any function or any other way) ?

Thanks.

[aduguid · Accepted solution]

You could use Power Query to bring it in as a source 

let
    // Define your domain controller and group
    DomainController = "your.domain.controller",
    GroupName = "YourGroupName",
    
    // Query Active Directory for group members
    Source = ActiveDirectory.Domains(DomainController),
    GroupMembers = Source{[Name=GroupName]}[Members],
    
    // Convert the result into a table
    MembersTable = Table.FromList(GroupMembers, Splitter.SplitByNothing(), {"Member"}),
    
    // Expand the result to include additional member details as needed
    ExpandedMembersTable = Table.ExpandRecordColumn(MembersTable, "Member", {"Name", "SamAccountName", "DistinguishedName"})
in
    ExpandedMembersTable

[v-yohua-msft · Accepted solution]

Hi, @ashishpbi 

As sroy_16 said,DAX (Data Analysis Expressions) itself does not have a built-in function to directly query Active Directory (AD) security group members. However, you can do this by using Power Query in Power BI, which allows you to connect to Active Directory and retrieve the necessary data. You can refer to the following links:

Solved: PowerBI to Query AD Group Memberships - Microsoft Fabric Community

Getting a list of AD Groups and their members using PowerQuery | Matt40k

How to Get Your Question Answered Quickly 

Best Regards

Yongkang Hua

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

[sroy_16]

Hello @ashishpbi 

Using DAX you cannot read Active Directory security group members, but however you can do the same using power query and M language.

Below is an example you can try. rename your filename accordingly.
let

Source = Csv.Document(Web.Contents("https://example.com/ADGroupMembers.csv"), [Delimiter=",", Columns=2, Encoding=65001, QuoteStyle=QuoteStyle.None]),
PromotedHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true])
in
PromotedHeaders

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos.

[aduguid · Accepted solution]

You could use Power Query to bring it in as a source 

let
    // Define your domain controller and group
    DomainController = "your.domain.controller",
    GroupName = "YourGroupName",
    
    // Query Active Directory for group members
    Source = ActiveDirectory.Domains(DomainController),
    GroupMembers = Source{[Name=GroupName]}[Members],
    
    // Convert the result into a table
    MembersTable = Table.FromList(GroupMembers, Splitter.SplitByNothing(), {"Member"}),
    
    // Expand the result to include additional member details as needed
    ExpandedMembersTable = Table.ExpandRecordColumn(MembersTable, "Member", {"Name", "SamAccountName", "DistinguishedName"})
in
    ExpandedMembersTable