Power BI RLS Complex Scenario

[shashanm]

Hi, So I’m embedding a Power BI report in an application and I’m trying to implement RowLevelSecurity in Power BI to restrict access to data based on the User Permissions. 

Tables - 

1. Order Level - The table on which the RLS needs to be applied. Each Order in this table has a BillTo, Shipper, Receiver & Fleet Code
2. UserPermissions table - This table has permissions defined in the following way -

OR I can still group by UserId & Group to get it in the following way (why? please read-on)

A user can have access to one or more of these codes and these codes come from different groups. There is one catch though - 

1. For a given group, the User should have access to Orders which have ALL (not ANY) of the codes matching. If a User has access to 2 BillTo Codes, 2 Shipper & 1 Receiver code from a dataset then my filter condition on OrderLevel would be - WHERE BillTo in (BillToCodes) AND Shipper in (ShipperCodes) AND Receiver in (ReceiverCodes)
2. We do this on all groups the User has access to & finally do a UNION (NOT INTERSECTION) of the OrderList from each of these groups

A User can have access defined using 1 or n groups. The access can be defined based on any of these codes (A User can have access to only BillTo or only Shipper or only Receiver or a combination of BillTo, Shipper or BillTo, Receiver or Receiver, Shipper codes or all 3)

See the expected result below to know the logic in its entirety -

I’m able to pass the UserId to Power BI in the embed token and filter the UserPermissions table based on the User login. 

My next steps would be to - 

1. Identify the distinct groups a user has access to (each user can have 0 to n)
2. Loop over these groups
3. For each group, get the Codes (one or more types) the User has access to (user can have one or more types of codes)
4. Identify Order Nos which have ALL of these codes matching & store them in a variable
5. Add Order Nos from all the groups to this variable
6. Use this variable to filter the OrderLevel table using the OrderNos from the above variable

I'm able to pass the User Id in the embed token & I already have an RLS in place with the help of a DAX query, but thtat's a very simplistic case. Any help on solving this is appreciated. Thanks in advance!!

[GilbertQ]

Hi @shashanm 

Could you look at dynamic RLS and see if that will work?

https://www.kasperonbi.com/power-bi-desktop-dynamic-security-cheat-sheet/