RLS to filter ONLY the dimension table

[raschaoot]

I have a question that to me seems like a common use case, but after a few hours of googling and endless youtube videos I somehow have not found the answer.

I have a semantic model (Microsoft Fabric, Direct Lake, fixed identity) with a few dozen tables around a central fact table with performance data (revenues, sales, hours, etc). Pretty standard dimensions like clients, opportunities, projects, etc. Key point is that all this performance data is in principe public to everyone with access to the model.

Exception is that one of the dimension tables is itself a mapping table (dubbed "- HR reporting" below) with identifiers that in turn link to other dimension tables with people data. Some of these are public - viewable to all, e.g. department or function title - and others are restricted to the reporting line - e.g. contract info, individual targets or salaries - or even to just HR (e.g. privacy sensitive data). Illustrated below.

How do I setup RLS in such a way that it filters the private dimension tables only, but does not filter the "- HR reporting" mapping table? I still want the people and their public data to show up, just not not their private info.

The only case I found working is when cardinality of a relationship is N:N, in which case I can change the filter direction to the reverse. But I'm not sure this is what I want either, as this wouldn't allow a line manager to select a dimension in that table to see the performance of just people that satisfy the selected criteria.

Ideally I'd avoid making duplicate semantic models, because there is another two dozen tables and corresponding measures of which a large part would have to be duplicated and maintained. In the end it's all about slicing performance data, in which managers have more visibility for their own reporting line.

So... is there no RLS option "do not cascade to relationships" or something along those lines? Or do I need a different setup to achieve what I need? 

Hope you can help - thanks 🙂

[danextian]

Hi @raschaoot 

When RLS is applied to a dimension that has relationships flowing to other tables, it will also extend to those other tables so you cannot restrict it just to that dimension table alone.

[nilendraFabric]

Hello @raschaoot 

The core challenge is that in Microsoft Fabric (as in Power BI), row‐level security filters are applied across active relationships in your model. This means that when you define a filter on a sensitive or “private” dimension table, that filter typically propagates to related tables—including your mapping (“– HR reporting”) table—without a built‐in option to stop the cascade.

Rather than applying RLS on the entire table, you might consider creating DAX measures that test user permissions and selectively return values. This approach can bypass unwanted filter propagation

if this is helpful please give kudos and accept the answer 

[raschaoot]

Thanks for the response. At least that confirms my suspicions, otherwise there would have been a "don't cascade security filter" option in the relationship settings.

Let's say a user pulls in contract type (a restricted field) - normal business user should see blank dimensions but still the total, whereas a manager should see the split for own reporting line and a blank for the rest. I can't use DAX to affect the dimension values, correct (as calculated columns are not supported in Direct Lake - but in any case there would be too many)? Or, if I'm mistaken, can you point me to an example?

If this not possible, I suppose splitting the models is the only way. 

Thanks again!

[nilendraFabric]

• you are correct that you cannot use DAX-calculated columns in Direct Lake mode. This is because calculated columns are not supported in Direct Lake semantic models due to performance and architectural constraints
  
  

Just a thought 

if possible Move the logic for restricted fields (e.g., contract type visibility) to your data warehouse or ETL pipeline. Create a column that pre-defines whether a user sees “blank” or actual values based on their role.
• For example:
• Add a column like `VisibleContractType` in your source table that outputs either the contract type (for managers) or `NULL`/blank (for normal users).
• This avoids reliance on calculated columns in Direct Lake while ensuring the logic is handled efficiently.

if this helps please accept the answer and give kudos

[raschaoot]

Hey, thanks for chipping in again. I'm not sure I follow.

I have lots of users, each with access to rows depending on another mapping table (the reporting line, not yet visualized), and the pipeline itself is obviously not user-aware. Would your suggestion involve that I duplicate the data for every possibility basically?

[v-xinc-msft]

Hi @raschaoot ,

nilendraFabric's suggestion was to move the logic for restricted fields into a data warehouse or ETL pipeline and create a column to predefine whether a user sees actual or blank values based on user role. This is a great way to avoid dependency on calculated columns in Direct Lake while ensuring the logic is handled efficiently. This suggestion doesn't necessarily mean you need to replicate the data for each possibility.

You could create a mapping table in your data warehouse or ETL pipeline that contains user roles and the types of contracts they can access. This way, you can dynamically filter data based on user roles at query time, without having to replicate the data for each possibility.

Or create views in the data warehouse to filter data based on user roles. Views can dynamically generate datasets without actually copying the data.

Also try pre-processing the data based on user roles during the ETL process and storing the results in a new table. This ensures that the data has been filtered based on user roles when it is loaded into the data warehouse.

Best regards,

Lucy Chen