Forum Discussion

GimmeDaData's avatar
GimmeDaData
Regular Visitor
3 years ago
Solved

Dynamic RLS / 2 conditions based on 2 columns

Hello,   I need to set up a dynamic RLS. I need users to be able to only see data related to their category, which is easy ("[PrincipalName] = userprincipalname()" filter on permission table and a ...
Data Protection
Need Help
  • GimmeDaData's avatar
    GimmeDaData
    3 years ago

    Hey,

     

    Thanks for your answer. 

     

    I think I solved it. I brought the Product Manager into the fact table and simply set this RLS filter on that fact table:

     

    VAR user_name =
    LOOKUPVALUE (
        'Permissions Table'[Name],
        'Permissions Table'[Email- (Short Form)], USERPRINCIPALNAME()
    )
VAR user_categories =
    CALCULATETABLE(
       VALUES ('Permissions Table'[AccessToCategory] ),
       FILTER(
          'Permissions Table',
          'Permissions Table'[Email- (Short Form)] = USERPRINCIPALNAME()
       )
    )
RETURN
    OR (
        'FactTable'[ProductManager] = user_name,
        'FactTable'[Category] IN user_categories
    )

     

     

    I know it is not necessarily considered a best practice to set a RLS filter on a fact table but it doesn't seem to impact performance as much.

     

    Thanks again for the tip 🙂

     

    Kid regards,

    GimmeDaData

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi GimmeDaData ,

 

I think you can try equal or not equal rule in your RLS.

For reference:

Dynamic Row-Level Security in Power BI with NOT IN or NOT EQUAL Rule

Dynamic Row Level Security w/ Multiple Criteria

If this reply still couldn't help you solve your issue, please show me a screenshot with the result you want.

For example, when Joey Tribbiani sign in, then your visual should look like what.

This will make it easier for me to find the solution. 

 

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • GimmeDaData's avatar
    GimmeDaData
    Regular Visitor
    3 years ago

    Hey,

     

    Thanks for your answer. 

     

    I think I solved it. I brought the Product Manager into the fact table and simply set this RLS filter on that fact table:

     

    VAR user_name =
    LOOKUPVALUE (
        'Permissions Table'[Name],
        'Permissions Table'[Email- (Short Form)], USERPRINCIPALNAME()
    )
VAR user_categories =
    CALCULATETABLE(
       VALUES ('Permissions Table'[AccessToCategory] ),
       FILTER(
          'Permissions Table',
          'Permissions Table'[Email- (Short Form)] = USERPRINCIPALNAME()
       )
    )
RETURN
    OR (
        'FactTable'[ProductManager] = user_name,
        'FactTable'[Category] IN user_categories
    )

     

     

    I know it is not necessarily considered a best practice to set a RLS filter on a fact table but it doesn't seem to impact performance as much.

     

    Thanks again for the tip 🙂

     

    Kid regards,

    GimmeDaData