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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Pragadeesh_M
Regular Visitor

DRLS in Multiple columns

Hi, Iam having a difficulty in implementing Dynamic row level security for below given scenario. I have two tables ( User Master and Main data table). Once the user logs in code should compare the columns in user master table ( Branch code, Product code, Access level,Vehicle type) and display the data in main table which is associated with the logged in user. Also if the logged in user access level is M, Then no filtering should occur and he should be able to view the entire data. Request you all to help. Also kindly note user details will not be avilable in Main data table, we need to compare the four columns only ( Branch code, Product code, Access level,Vehicle type)

 

User Table sampleUser Table sampleData table sampleData table sample

 

1 ACCEPTED SOLUTION
hnguy71
Memorable Member
Memorable Member

Hi @Pragadeesh_M ,

 

Interesting set-up.

Typically you would build RLS against dimension tables but seems you have all your data into one flat table. However, to accomplish this with your current model you'll have to ensure you disconnect the two tables.

 

To make things easier, you'll need to create a key column in both your Main and User tables. The key will be your primary 3 columns you want to check against: branch, produce, and vehicle. Something like this:

Access.Key = COMBINEVALUES("^", [Branch Code], [Product Code], [Vehicle Type])

 

And then within your Manage Roles configuration, the DAX expression against your Main table would similar to this:

VAR _UPN = USERPRINCIPALNAME()
VAR _isMaster = COUNTROWS(CALCULATETABLE(YOUR_USER_TABLE, YOUR_USER_TABLE[User Mail] = _UPN, YOUR_USER_TABLE[Access Level] = "M")) >= 1
VAR _IAM = IF( _isMaster, TRUE(), [Access.Key] IN CALCULATETABLE(VALUES(YOUR_USER_TABLE[Access.Key]), YOUR_USER_TABLE[User Mail] = _UPN))

RETURN

_IAM

 

Of course, you'd replace YOUR_USER_TABLE with your actual table name. 



Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!

View solution in original post

2 REPLIES 2
hnguy71
Memorable Member
Memorable Member

Hi @Pragadeesh_M ,

 

Interesting set-up.

Typically you would build RLS against dimension tables but seems you have all your data into one flat table. However, to accomplish this with your current model you'll have to ensure you disconnect the two tables.

 

To make things easier, you'll need to create a key column in both your Main and User tables. The key will be your primary 3 columns you want to check against: branch, produce, and vehicle. Something like this:

Access.Key = COMBINEVALUES("^", [Branch Code], [Product Code], [Vehicle Type])

 

And then within your Manage Roles configuration, the DAX expression against your Main table would similar to this:

VAR _UPN = USERPRINCIPALNAME()
VAR _isMaster = COUNTROWS(CALCULATETABLE(YOUR_USER_TABLE, YOUR_USER_TABLE[User Mail] = _UPN, YOUR_USER_TABLE[Access Level] = "M")) >= 1
VAR _IAM = IF( _isMaster, TRUE(), [Access.Key] IN CALCULATETABLE(VALUES(YOUR_USER_TABLE[Access.Key]), YOUR_USER_TABLE[User Mail] = _UPN))

RETURN

_IAM

 

Of course, you'd replace YOUR_USER_TABLE with your actual table name. 



Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!

Hi. @hnguy71. It worked perfectly. Thank you so much

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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