RLS group with DAX

Posted On 2025-09-05

When we talk about RLS, the first thing that comes to mind might be to set rules like “if your username matches this, you can see ‘your own’ data” or we’ll make a rule that simply says “Company = Contoso” and then we have to put people in that roles to make them see Contoso.

Here’s the thing; As long as your DAX formula can equal true/false, you can use it for RLS

Well almost, at least. In most cases. Let’s not dwell on outliers and get back on track with the good stuff!

This means we can use USERPRINCIPALNAME() to look up another value and then filter on that value, like Company and Department.

Let’s say we have this very simple dashboard. The model behind this only has 2 tables.

I have one facts table with transactions and one user table where OwnerID relates to ID in User.

It’s crucial that I have the UPN in the user table to determine who’s who, since we’re going to use the USERPRINCIPALNAME() function in DAX.

When setting up RLS, I prefer to simply make a new column in the table I want to filter since it’ll give me the results and I’m able to troubleshoot more visually. At the moment, I don’t actually have an account in this list so I’ll hardcode one of the user names into my DAX.

I merely state that the UPN is a string and whenever the statement is true, it’ll say so in the column. If I used this as a condition in RLS, We would only see Oscars row and whatever that row is related to in the model. So far so good.

So now let’s instead make a new DAX formula so we fetch whatever value Oscar has as his company.

CALCULATE(
    SELECTEDVALUE(dUsers[Company]),
    FILTER(
        dUsers,
        dUsers[UPN] = "oscar.thomas@contoso.com"
    )
)

As you can see, his value is now replicated on all rows. Now we just need to make the check to see if that value is the same as the current rows company value.

RLS Check = 

CALCULATE(
    SELECTEDVALUE(dUsers[Company]),
    FILTER(
        dUsers,
        dUsers[UPN] = "oscar.thomas@contoso.com"
    )
) = dUsers[Company]

Now we can see that this works great! It’s true on all rows where the user belongs to the same company as the selected user and false on everyone else.

Now let’s copy that code and go into RLS to make 3 roles! One role is for Company, one is for Department and one is for the combination of it!

In the combined DAX, I’ve written it like this:

AND(
    CALCULATE(
        SELECTEDVALUE(dUsers[Company]),
        FILTER(
            dUsers,
            dUsers[UPN] = USERPRINCIPALNAME()
        )
    ) = dUsers[Company],
    CALCULATE(
        SELECTEDVALUE(dUsers[Department]),
        FILTER(
            dUsers,
            dUsers[UPN] = USERPRINCIPALNAME()
        )
    ) = dUsers[Department]
)

Note that I used USERPRINCIPALNAME() this time and not a hardcoded username. The reason why is because when I test RLS roles, I can impersonate other Users and simply specify what Username I want to use

Now with Department, I get to see all users with the same department as I belong to myself!

OK so I have 3 roles now and that gives me the ability to showcase some different views.

I could look at the report and see all the data from whatever Company I myself belong to or whatever department I belong to like this

I also have these examples from the roles I just made. First it’s a combination of the two above where you have both roles active on the same time. Since RLS roles stack, it means that you get to see all user that belong to either the same company or the same department as you, so we get a higher value.

On the right side, we have the role that combines company and department meaning you only get to see the users that are both in the same company and the same department as you. This, of course, gives the smallest user group and thus the lowest value in the reporting view.

So why would you use something like this?

Well I hope it expands your mindset on RLS to not only filter on the users own row, but rather use the Userprinciplename to look up another value and then use that value to filter the (or another) table.

Now imagine you have 30 different companies and thousands of users. You don’t need to create 30 roles to get one per company. With one single role you have a dynamic role that will give 30 different accesses depending on who you are, as the report viewer.

Cheers!