Conditionally filtering rows from a table with another table

[jeffgreenrc]

Hi,

I have scenario, where i would like to implement RLS security with current login user. I have three tables i.e.

UserRole

UserId	Role
Admin	System Admin
User1	AR clerk
User1	AP manager
User1	Sales Clerk 1
User2	Sales Clerk 2
User2	System Admin

UserLocationPermission

UserId	Location	CanViewInventory
User1	Miami	1
User1	Seattle	0
User2	Los angles	1
User2	New York	1

Inventory

Location	Item	Quantity
Miami	Item1	10
Seattle	Item2	20
Los angles	Item3	30
New York	Item4	40

When User1 logins: (limited data of only 1 location since canviewinventory is enabled for it)

Inventory

Location	Item	Quantity
Miami	Item1	10

When User2 logins: (can view all data since it has system admin role)

Inventory

Location	Item	Quantity
Miami	Item1	10
Seattle	Item2	20
Los angles	Item3	30
New York	Item4	40

When Admin logins: (can view all data since it has system admin role)

Inventory

Location	Item	Quantity
Miami	Item1	10
Seattle	Item2	20
Los angles	Item3	30
New York	Item4	40

Pls guide me on how can I implement dynamic filtering of rows of Inventory table in visual in Power BI based on current user login.

Thank you.

[jeffgreenrc]

Thanks @danextian ,

Pls provide more detail on your solution. Should i define relationship between tables and where should i define the filter condition you have provided.

Sorry for my ignore as I am new to power bi.

[danextian]

Create a many-to-many single direction relationship from UserRole[UserID] to UserLocationPermission[UserID]

Go to modelling view. Go to manage roles. Look for UserRole table and create a role using either of the formula given (note: switch to dax editor view). In the service, go to security settings and add the users to the role - users need to be added manually in the service or if they are in a security group, use that security group.

Also, there is a link from @parry2k, have you had a look at it?

[jeffgreenrc]

Thanks @danextian ,

I am able to filter userrole and userlocationpermission but not Inventory. 

The reason is i can not make active relationship between inventory and userlocationpermission because admin would not have records in the userlocationpermission since they can view all location.

I am trying to implement RLS on inventory table with

Location IN values(LocationPermission[Location])

but the above is not working and giving error, even lookupvalue is not working.

How can i filter inventory table records without active relationship with locationpermission table.

[V-yubandi-msft · Accepted solution]

Hi @jeffgreenrc ,
In your scenario, you can set up the RLS filter directly on the Inventory table. This will allow admins to view all rows, while other users will only see rows permitted by their location access. You can use an expression like the following.

VAR CurrentUser = USERPRINCIPALNAME()
VAR IsSystemAdmin =
    CALCULATE (
        COUNTROWS ( UserRole ),
        UserRole[UserId] = CurrentUser &&
        UserRole[Role] = "System Admin"
    )
RETURN
    IF (
        IsSystemAdmin > 0,
        TRUE(),  
        Inventory[Location] IN
            CALCULATETABLE (
                VALUES ( UserLocationPermission[Location] ),
                UserLocationPermission[UserId] = CurrentUser,
                UserLocationPermission[CanViewInventory] = 1
            )
    )

Here’s an example expression you can try, please test it, and let us know how it goes.

This logic determines whether the current user has the System Admin role. If so, they are not subject to filtering and can view all rows. If not, the filter limits Inventory rows to locations where the user has permission (CanViewInventory = 1).

Please implement this as the RLS rule on the Inventory table and test it using  View as Role in Power BI Desktop to ensure it works correctly for your users.

Regards,
Yugandhar.

[jeffgreenrc]

Thanks a lot !

[jeffgreenrc]

Thank you for responses. 

The issue is I cannot make the relationship between tables active since I dont want to retrieve records from Inventory table based on it. The reason is the, the Admin user record is not present in UserLocationPermission table. 

Therefore, I need to identify if the user belongs to System role then view all records i.e. make the join inactive.

If user does not have system admin role, then it needs to show records matching with UserLocationPermission and Inventory table.

So how can I dynamically filter records in inventory table.

[danextian]

Hi @jeffgreenrc 

I am assuming your role table already includes the email addresses. Try the following RLS expressions:

VAR CurrentUser =
    USERPRINCIPALNAME ()
VAR CurrentRole =
    CALCULATE (
        MAX ( UserRole[Role] ),
        KEEPFILTERS ( UserRole[Email] = USERPRINCIPALNAME () )
    )
RETURN
    IF (
        CurrentRole = "System Admin",
        TRUE (),
        // admin  -  full access
        RELATED ( UserRole[Email] ) = CurrentUser // non-admin -  only related rows
    )


==============================

VAR CurrentUser =
    USERPRINCIPALNAME ()
VAR CurrentRole =
    LOOKUPVALUE ( UserRole[Role], UserRole[Email], CurrentUser )
RETURN
    IF (
        CurrentRole = "System Admin",
        TRUE (),
        // admin - full access
        RELATED ( UserRole[Email] ) = CurrentUser // non-admin - only related rows
    )

Since your userid repeats, you might end up creating a many-to-many relationship from UserRole to permission tables. Ensure that it is single direction only. 

[parry2k]

@jeffgreenrc it is pretty straight to setup, check out this blog post and make the changes as per your scenario