Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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 MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
18 | |
15 | |
12 | |
11 | |
8 |
User | Count |
---|---|
24 | |
18 | |
12 | |
11 | |
10 |