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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors