Dynamic RLS / 2 conditions based on 2 columns

[GimmeDaData]

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 relationship between it and the fact table on Category, with a bridge table in between).

But some users are also managers of specific products, which can be sold outside users' categories and they should be able to follow those products as well. My problem is that I don't know how to make a user see the the data related to a category PLUS products (which they are managers of) outside their category.

There are also users, who are NOT managers of any products, they just need to see a category/categories (I guess for them it would be easiest to create a separate role).

Permissions would be managed by an excel file in sharepoint. It is already being used to manage RLS access to other reports (which don't have the product manager problem). It looks like this:

Permissions Table

name	PrincipalName	category
Joey Tribbiani	jt@abc.com	sandwiches
Chandler Bing	cb@abc.com	taco
Chandler Bing	cb@abc.com	pizza
Rachen Green	rg@abc.com	salads
Monica Geller	mc@abc.com	tacos
Monica Geller	mc@abc.com	pizza
Monica Geller	mc@abc.com	salads
Monica Geller	mc@abc.com	sandwiches

I am pulling this table into my model of course. None of these columns have unique values, I have to create a connector/bridge table, to avoid many-to-many relationships.

I have a Product Dimension table and a fact table. Product Managers' names (names, not PrincipalNames) are in Product Dimension (but I could pull them into the fact table if needed). However Category column has to be in my fact table (as it is not based on a product but rather a transaction in the fact table).

Product Dimension

ProductID	ProductManagerName	Lifecycle
1	Joey Tribbiani	Active
2	Joey Tribbiani	Active
3	Ross Geller	Active
4	(blank)	End of life
5	Chandler Bing	Active

Fact Table

TransactionID	ProductID	Category	Value
1	1	sandwiches	5
2	1	pizza	5
3	2	pizza	10
4	3	salads	7
5	4	salads	8
6	5	taco	9

Any ideas how to achieve this? Is it even possible?

Thanks.

[Anonymous]

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 · Accepted solution]

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