Dynamic Row Level Security

[jmwdba]

So I was trying to following the post at RLS security/Hierarchy levels but I think my scenario might be slightly different. Row Level Security from a Dynamic Standpoint has been a challenge for me and there are simply too many combinations to manually create the roles.

What I want is my Cost Center UserTable to control the Worldwide Budget table based on the user and the Cost Center they are permitted to see. Inside of the Cost Center UserTable is the Cost Center which matches the Cost Center in the Worldwide Budget Table and also the users email address as well as their user ID.

Because its education data, I cannot display the information in detail I I created a sample file for each table to help you get an idea. As you can see the Cost center becomes the RLS item and because there are about 100+ of these I am trying to avoid having to type all of these individually.

Cost Center UserTable.xlsx (9.6 KB)
Worldwide Budget.xlsx (9.3 KB)

[Melissa]

Hi @jmwdba,

Give this a go.
I’ve set up the model so filters only propagate from Cost Center to the fact Budget

Created a CostCenter role with these filters:
on Budget table

on Cost Center table

Now select “View as” + Role + User

Here’s the sample file:
eDNA - Dynamic RLS.pbix (29.5 KB)

I hope this is helpful

[jmwdba]

You are a rockstar!!! One question I have. There are 5 individuals that I need to give access to see all of the data is there a way to enter their unique IDs into the DAX expression to accomplish this?

[Melissa]

@jmwdba you could but then you’d also have to maintain it there. That would definitely not be my choice. Instead think about adding those exceptions to the model, so you can update that without having to hard code anything a DAX expression.

[jmwdba]

When you say add it to the model do you mean add those users into the Cost Center file display for each cost center? If so, the downside of that there is 422 cost centers that I would have to enter the individuals for.

[Melissa]

No a separate table with just those users emails will do.

[jmwdba]

I am trying to think this through and visualize it. So if I took the below email addresses as examples would I need to create a table that has each email address as well as each possible cost center or is it just a table of the email address? I would probably call this table “Cost Center Administration”.

user1@sample.edu
user2@sample.edu
user3@sample.edu
user4@sample.edu
user5@sample.edu

[Melissa]

Okay here we go @jmwdba . I’ve reused your sample and…

Created a hidden- and disconnected table

Added an AllAccess column to your Cost Center table that contains TRUE for all rows

Changed the “Managed Roles” filter logic removed it from the Cost Center table and changed it for the Budget table, like below.

VAR _HasAllAccess =
IF(
    COUNTROWS( 
        FILTER( ALL( AllAccess[User] ),
            AllAccess[User] = USERPRINCIPALNAME()
        )) >0,
    1, 0
)
VAR _AllCostCenter =
CALCULATETABLE(
    VALUES( 'Cost Center'[Cost Center] ),
    'Cost Center'[AllAccess] = TRUE()
)

VAR _GetCostCenter =
CALCULATETABLE(
    VALUES( 'Cost Center'[Cost Center] ),
    'Cost Center'[PowerBI Access Email] = USERPRINCIPALNAME())
RETURN

SWITCH( TRUE(),
    _HasAllAccess = 0 && [Cost Center] IN _GetCostCenter, TRUE(),
    _HasAllAccess = 1 && [Cost Center] IN _AllCostCenter, TRUE(),
    FALSE()
)

.
with this result.

or

.
Here’s the sample
eDNA - Dynamic RLS v2.pbix (32.3 KB)

I hope this is helpful

[jmwdba]

I am looking at this and comparing to my file. I cannot seem to make it work so I am checking to make sure I didn’t miss a step.

UPDATE: Figured it out. One of my columns had an extra space in it. All is well and working now!