Forum Discussion
Conditionally filtering rows from a table with another table
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.
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.
8 Replies
jeffgreenrc it is pretty straight to setup, check out this blog post and make the changes as per your scenario
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.
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.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.
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?
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.
