Dynamic Row Level Security with different access level for different reports

[Hazzzy]

I have a semantic model in Power BI where I have two data tables: Sales and P/L. I also have two dimension tables: Market and User.

The Market dimension table is mapped to both Sales and P/L data tables.

I need to set up dynamic row level security where user access should be based on the following:

P/L: User will only see data for the market they are responsible for.

Sales: Users will see sales for all the markets except one.

What's the best way to implement this? Any help would be highly appreciated! 

[v-sgandrathi · Accepted solution]

Hi @Hazzzy,

Thank you for reaching out to the Microsoft Fabric Community!

Based on your requirements, you can achieve Dynamic Row-Level Security (RLS) by defining two different security rules within Power BI:

• This can be achieved by filtering the MarketID in the P/L table based on the user’s assigned market from the User table.
• This can be implemented by applying a negative filter that excludes the restricted market from the user's access.

Suggested Approach:
Ensure that your User table contains information about the markets each user has access to (e.g., ResponsibleMarket and ExcludedMarket).
Implement Dynamic RLS filters using DAX within the Power BI Manage Roles feature:

• For P/L: Apply a filter so users only see data for ResponsibleMarket.
• For Sales: Apply a filter to exclude ExcludedMarket from the accessible data.

Since the Market dimension is linked to both Sales and P/L tables, the RLS filters applied on MarketID should propagate to the fact tables accordingly.

Please Accept as solution if this meets your needs and a Kudos would be appreciated.

Thank you.

[v-sgandrathi]

Hi @Hazzzy,

I wanted to follow up on our previous suggestions regarding the issue. We would love to hear back from you to ensure we can assist you further.

If our response has addressed your query, please accept it as a solution and give a ‘Kudos’ so other members can easily find it. Please let us know if there’s anything else we can do to help.

Thank you.