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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors