Forum Discussion

jeffgreenrc's avatar
jeffgreenrc
Regular Visitor
9 months ago
Solved

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 A...
Need Help
Tips and Tricks
Tutorial Requests
  • V-yubandi-msft's avatar
    V-yubandi-msft
    9 months ago

    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's avatar
jeffgreenrc
Regular Visitor
9 months ago

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's avatar
    danextian
    Super User
    9 months ago

    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's avatar
      jeffgreenrc
      Regular Visitor
      9 months ago

      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's avatar
        V-yubandi-msft
        Community Support
        9 months ago

        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.