How to setup RLS with mutiple filters?

[ptmuldoon]

I am revisiting (not quite starting from scratch) in working to rebuild a data model, and trying better to understand and use RLS.  Currently this is in desktop, but the model will also likely get rebuilt later in PBI service.

I am trying to set this up and design so that based on the UPN of the user, when the user views the model, they should get filtered down into 3 main criteria.

1. Filter the Companies/Divisions based on the Group they belong to.

2. Filter the Departments they are allowed to see (ie, Sales, Marketing, etc.)

3. Filter the COA for the appropriate access (ie, Revenue Only, Expense Only, No Labor, etc.)

I've created a sample file trying to getting this started creating various tables and 'lists' for each, and think i'm on the correct path but unsure just yet.  The model look likes below, all using 1 to many relationships.  And I've created a Role filtering the User email to the UPN.   It's just those next steps I think I need to better learn and understand?

I'm not sure if I can attach the sample, but have placed it here as well if anyone can help.

https://drive.google.com/file/d/1xH3Vc4PncX_FEEgLbnGIzvSF-Tlk2qve/view?usp=sharing

[ptmuldoon]

So I've updated my Sample file here below, and feel like I'm pretty close now in getting this to work as I would like.

https://drive.google.com/file/d/1xH3Vc4PncX_FEEgLbnGIzvSF-Tlk2qve/view?usp=sharing

I was able to setup the RLS where based on the UPN, I have this:

Filtering Correctly to get the Correct 'Regions', Departments', and 'Account Types'.  But I haven't figure out quire the last piece, and this filtering the actual GL Accounts to then only show the correct accounts for those departments.  

I'm not sure I need to add additional RLS into the Role (in the GLAccountLIsts)?  Or I need to do additional with the relationships.

I ended up using this RLS on each of my DeptGroups, SiteGroups, and GLAccountGroup tables.

[DeptGroupID] IN SELECTCOLUMNS(
    FILTER(
        Users,
        Users[Email]=USERPRINCIPALNAME()
    ),
    "DeptGroupID"
    ,[DeptGroupID]
)

[Anonymous]

Hi @ptmuldoon

Belated reply.

If you need to combine multiple filters, you can use logical operators in the DAX expression. You can try, for example:

[DeptGroupID] IN SELECTCOLUMNS(
    FILTER(
        Users,
        Users[Email] = USERPRINCIPALNAME()
    ),
    "DeptGroupID",
    [DeptGroupID]
) &&
[AccountType] IN SELECTCOLUMNS(
    FILTER(
        Users,
        Users[Email] = USERPRINCIPALNAME()
    ),
    "AccountType",
    [AccountType]
)

Regards,

Nono Chen

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

[ptmuldoon]

Hi @Anonymous 

I've made some progress with that.  But do you know if there is away to possible pass a comma separate list into an IN statement?

VAR Departments = "Administration, Maintenance"

RETURN
DeptLists[DeptGroupName] IN {Departments}

[Anonymous]

Hi @ptmuldoon 

Please try:

Filter Companies/Divisions Based on Group:
Ensure your Users table has a field that links to the DeptGroups table.
Create a relationship between Users and DeptGroups using the DeptGroupID.

In your RLS role, use a DAX filter like:

[DeptGroupID] = LOOKUPVALUE(DeptGroups[DeptGroupID], DeptGroups[DeptGroupName], USERPRINCIPALNAME())

Filter Departments:
Ensure your Users table has a field that links to the DeptLists table.
Create a relationship between Users and DeptLists using a common field like SuffixUPNName.

In your RLS role, use a DAX filter like:

[Departments] IN VALUES(DeptLists[Departments])

Filter COA for Appropriate Access:
Ensure your Users table has a field that links to the AccountsLists table.
Create a relationship between Users and AccountsLists using a common field like SuffixUPNName.

In your RLS role, use a DAX filter like:

[AccountType] IN VALUES(AccountsLists[AccountType])

Implementing RLS in Power BI Desktop:
Go to the "Modeling" tab and select "Manage Roles".
Create a new role and add the DAX filters for each table as described above.
Test the roles by using the "View as Roles" feature to ensure the data is filtered correctly.

Publishing to Power BI Service:
When you publish your model to the Power BI Service, the RLS roles will be carried over.
Assign users to the roles in the Power BI Service under the dataset's security settings.

Regards,

Nono Chen

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