Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get inspired! Check out the entries from the Power BI DataViz World Championships preliminary rounds and give kudos to your favorites. View the vizzies.

Reply
khalilg
Regular Visitor

Dynamic RLS with complex security module

Hi,

I'm trying to create a Dynamic RLS for a complex security module,

here are the table structure and relationship:

 

Table 1. Users

UsernameCompany CodeRole TypeAccess ID
AAS 
BAA 
CAR77
DBB 

 

Table 2. Roles

UsernameAccess Detail
A12345
A67891
C77
D97

 

Table 3. Data

IDDateValueCompany CodeRole SRole BRole R
101-01-20211A12345 77
201-01-20212A67891  
301-05-20213A  77
410-01-20214B 97 

 

As it's showing in the above Data Table, User "A" should be able to see the first 2 rows as Role S 12345 & 67891 are linked to user A in Roles Table and user A is in Company "A", User “C” should be able to see row 1 and row 3 as Role R is 77 and 77 is linked to user C and the user is in Company “A”, and finally User “D” should be able to see only row 4 as Role B is 97 and user Company is “B”.
User “B” should be able to see everything as Role Type “A” is Admin.
So, the relationship here is:

  1. Table 1 (Username) -> Table 2 (Username)
  2. Table 1 (Company Code) -> Table 3 (Company Code)
  3. Table 1 (Role Type) -> Table 3 (Role S, Role B or Role R)
  4. Table 2 (Access Detail) -> Table 3 Value of (Role S, B or R)

I’ve tried different type of DAX expression but with no luck so far, any ideas that might help in resolving this?
Thanks in advance,

1 REPLY 1
lbendlin
Super User
Super User

@khalilg  Why do you have AccessID as part of the Users table?

 

It feels as if you need to have a concatenated list of Access Details in the Users table, and make them company specific to remove the dependency on Company Code.

 

Username Company Code Role Type Access Codes
A A S 12345|67891
B A A  
C A R 77
D B B 97

 

And then in the RLS rules you can check PATHCONTAINS()

 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code FABINSIDER for a $400 discount!

FebPBI_Carousel

Power BI Monthly Update - February 2025

Check out the February 2025 Power BI update to learn about new features.

March2025 Carousel

Fabric Community Update - March 2025

Find out what's new and trending in the Fabric community.