Query Active Directory using DAX

Question

Given my tabular model, I'm attempting to write a measure that changes behavior, depending upon which role the effective user belongs to. This isn't traditional row-level security (RLS) since I'm not trying to filter by role; just do an if-else, instead.

I've come across the following solution at https://community.powerbi.com/t5/Desktop/DAX-Expression-For-Role-Level-Security-Using-DirectQuery/td-p/489699, which I believe will work, but I'd prefer querying active directory to see if the user belongs to said role, rather than another table on the model.

I've also seen some articles (i.e. https://community.powerbi.com/t5/Desktop/How-to-leverage-Active-Directory-to-filter-the-data-in-Power-BI/td-p/140479) about getting attributes from active directory for Power BI, but nothing that exposes the DAX being used.

Bottom line, if I could get a role name in DAX or call a function to check if the user is in a role, I'd be golden (assuming performance isn't compromised).

Edit: I should add that I'm currently leveraging one of three functions to get the user. USERNAME(), USEROBJECTID(), and USERPRINCIPALNAME().

Jeff Jeff · Accepted Answer · 2019-12-06T16:51:46

I ended up giving up on leveraging active directory and did what it appears everyone else is doing (as seen in one of the links posted in my question).

For reference, here's a snippet that illustrates the solution:

EVALUATE
SUMMARIZECOLUMNS (
    "Some Measure",
    IF (
        LOOKUPVALUE('My User'[UsePrivilegedValue], [Name], USERNAME()),
        SUM('Some Fact'[PrivilegedValue]),
        SUM('Some Fact'[OtherValue])
    )
)

Query Active Directory using DAX

1 Answers