Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code FABINSIDER for a $400 discount.
Register nowGet inspired! Check out the entries from the Power BI DataViz World Championships preliminary rounds and give kudos to your favorites. View the vizzies.
Hi,
I'm trying to create a Dynamic RLS for a complex security module,
here are the table structure and relationship:
Table 1. Users
Username | Company Code | Role Type | Access ID |
A | A | S | |
B | A | A | |
C | A | R | 77 |
D | B | B |
Table 2. Roles
Username | Access Detail |
A | 12345 |
A | 67891 |
C | 77 |
D | 97 |
Table 3. Data
ID | Date | Value | Company Code | Role S | Role B | Role R |
1 | 01-01-2021 | 1 | A | 12345 | 77 | |
2 | 01-01-2021 | 2 | A | 67891 | ||
3 | 01-05-2021 | 3 | A | 77 | ||
4 | 10-01-2021 | 4 | B | 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:
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,
@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()
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code FABINSIDER for a $400 discount!
Check out the February 2025 Power BI update to learn about new features.
User | Count |
---|---|
23 | |
12 | |
10 | |
10 | |
10 |
User | Count |
---|---|
17 | |
14 | |
12 | |
11 | |
10 |