DRLS in Multiple columns

[Pragadeesh_M]

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 sampleData table sample

[hnguy71 · Accepted solution]

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. 

[Pragadeesh_M]

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