Dynamic RLS

[rav-goli]

Hello Everyone,

I have a Security table with a list User emails in one column and in the second colum I have entered a DAX expression

eg

Email                                      Access Level

john.doe@compnay.com     [Region] = "North"

Is there anyway in the 'Table filter DAX expression' part of Manage Roles  to lookup the Users access level and execute that DAX command?

Hope this is possible.

Thanks

[amitchandak]

@rav-goli , refer if these can help

Row Level security
https://community.powerbi.com/t5/MBAS-Gallery/Microsoft-Power-BI-Unleash-row-level-security-patterns...
https://radacad.com/dynamic-row-level-security-with-organizational-hierarchy-power-bi
https://www.blue-granite.com/blog/using-dynamic-row-level-security-with-organizational-hierarchies
https://docs.microsoft.com/en-us/power-bi/service-admin-rls
https://blog.tallan.com/2018/04/10/row-level-security-in-power-bi-part-1-roles-and-users/

[jdbuchanan71]

@rav-goli 

I assume you are trying to filter another table that also has region in it and each user has one or more regions they can see.  What you want in the security table is not the dax expression, you want just the regions that user can see.

Then we use a role to apply the filtering to the data table so the user only sees thier regions.

Modeling > Manage Roles > New Role

You add the filter to your data table.

VAR _UPN = USERPRINCIPALNAME()
RETURN 
    [Region] = 
    LOOKUPVALUE (
        Security[Region],
        Security[Region],Sales[Region],
        Security[Email],_UPN
    )

You can test the results by going to view as other user in modeling.

and it will show you what that user would see.

I have attached my sample file for you to look at.

[rav-goli]

Hi Jdbuchanan71,

Thanks for you prompt reply.

Yes i'm filtering another table.

The example I used was just a simple one, but I have quite a few Users whose requirement is a little more complex...example...

Jane.doe@company.name   [Region] = "North" &&  [Region] = "South"  || [Region] = "East" && [Group] = "Red"

That is why I was asking if I could somehow run the DAX expression straight from the value of the Lookup?

Thanks

[jdbuchanan71]

@rav-goli 

No, you cannot read the string and execute the dax.  If you have users that would need different filters (some region only, some region and color, etc.) you would need different roles as well.

Take a look at the artivles @amitchandak  shared, they will point you in the right direction.