Complexity RLS - Help please!

[PedroModa]

Hi everyone, good evening! I need some help.

Recently, a user reached out saying he couldn’t use one of our Power BI reports. At first, I found it strange because he was the only one reporting issues — everyone else has always used the report normally. It’s a clean, well-optimized report following best practices, so it didn’t make sense that only he was experiencing such extreme slowness.

I did a video call with him and confirmed the issue: the report was practically unusable, nothing would load — and again, this happened only for him. Even worse, his slowness was consuming a huge amount of my Fabric capacity (I’m using Embedded).

I started investigating and found that the problem was related to the RLS roles he is assigned to. He is a manager, so he belongs to 25 different RLS roles, each one associated with a cost center. Currently, all these roles share the same particularity: each RLS rule contains two cost center codes (an old one and a new one due to a recent incorporation). For example:

• Rule 1: Old Code 1 OR New Code 1
• …and so on, up to the 25th rule.

After digging deeper, I noticed that using OR inside RLS significantly hurts performance — and it seems this is exactly what is breaking the report for this specific user.

I tested replacing OR with IN, but the performance was exactly the same, with no improvement at all.

My question is: how can I solve this?

I thought about switching to dynamic RLS using USERNAME(). However, two problems came up:

1. Manual maintenance: I would constantly need to add and remove people from this table. And it would become a huge table, since we have almost 1,000 employees.
2. Limited flexibility: this approach works well if I only need to filter by cost center. But if I need to hide information from another table (like “section”), I don’t think it would solve that (at least as far as I understand).

Questions:

• Has anyone faced something similar?
• Is there a more efficient way to handle multiple complex RLS roles?
• Is there a modeling approach that avoids this performance drop, especially when a user belongs to many RLS roles?

Any help is greatly appreciated. Thank you!

[PedroModa]

@Ahmed-Elfeel Thank you so much for your help, things are starting to become clearer... could you help me think about how I would create this rule?

How would I create this dynamic RLS... The person can only see these coli-codcusto (table dcentrodecusto (table diensao)) [COLI-CODCUSTO] == "1-1.000010" || [COLI-CODCUSTO] == "2-1.000012"

in the dfornecedor table, this rule [CODCFO] <> "0009789"

in the dMedContratos table (dimension table), this rule [Contrato] == "" (that is, I don't want anything from this table to appear)

in the fAjusteGerencial table (fact table), this rule VAR TI = RELATED(dCentrodeCusto[NOME]) IN {"TECNOLOGIA DA INFORMACAO"} VAR ContaValida = NOT ( [Conta] IN { "Salários E Ordenados", "Hora Extra", "Encargos Trabalhistas", "Rescisões E Indenizações", "Prêmios E Bonificações" } ) RETURN (TI && ContaValida)

in the fBalancete table (fact table), this rule VAR TI = RELATED(dCentrodeCusto[NOME]) IN {"INFORMATION TECHNOLOGY"} VAR ValidAccount = NOT ( RELATED('dC Account'[Management Accounts]) IN { "Salaries and Wages", "Overtime", "Labor Charges", "Terminations and Compensation", "Bonuses and Bonuses" } ) VAR InitialExpense = NOT ( fBalance Sheet[COMPLEMENT2] = {"JOAO DA SILVA"} ) RETURN (IT && ValidAccount && InitialExpense)

in the fMeasurements table (fact table), this rule [CODCCUSTO] <> "" in the fPartida table (fact table), this rule [IDPARTIDA] == "195X195X195" (that is, I don't want it to see anything)

in the fpitempartida table (fact table), this rule [CHAPA] == "195X195X195" (that is, I don't want it to see anything)

in In the `ftmovitens` table (fact table), this rule: `VAR TI = RELATED(dCentrodeCusto[NOME]) IN {"TECNOLOGIA DA INFORMACAO"} VAR ContaValida = NOT ( RELATED('dC Conta'[Contas Gerenciais]) IN { "Salários E Ordenados", "Hora Extra", "Encargos Trabalhistas", "Rescisões E Indenizações", "Prêmios E Bonificações" } ) RETURN (TI && ContaValida)` Bonuses" } ) RETURN (TI && ContaValida)

and in the table fvalorçado (fact table), this rule VAR TI = RELATED(dCentrodeCusto[NOME]) IN {"INFORMATION TECHNOLOGY"} VAR ContaValida = NOT ( [Conta] IN { "Salaries and Wages", "Overtime", "Labor Charges", "Terminations and Compensation", "Bonuses and Bonuses" } ) RETURN (TI && ContaValida)

[Ahmed-Elfeel · Accepted solution]

Hi @PedroModa,

For Sure, Here is Simple and clear solution Step by Step you can Do :

First Step is to Create Security Table called UserSecurity with all user permissions like this:

UserEmail	CostCenter	RestrictedSupplier
manager@powerbi.com	1-1.000010
manager@powerbi.com	2-1.000012
manager@powerbi.com	1-1.000010	0009789
Until you reach end (25)	etc

Second Step is to put RLS Rules for Each Table

• dCentrodeCusto (dimension):

[COLI-CODCUSTO] IN VALUES(UserSecurity[CostCenter])

•  dFornecedor (dimension):

NOT([CODCFO] IN VALUES(UserSecurity[RestrictedSupplier]))

•  dMedContratos (hide entire table):

[Contrato] IN {""}  // Or use FALSE() to hide everything

• fAjusteGerencial (fact table):

VAR TI = RELATED(dCentrodeCusto[NOME]) = "TECNOLOGIA DA INFORMACAO"
VAR ValidAccount = NOT([Conta] IN { 
    "Salários E Ordenados", 
    "Hora Extra", 
    "Encargos Trabalhistas", 
    "Rescisões E Indenizações", 
    "Prêmios E Bonificações" 
})
RETURN TI && ValidAccount

• fBalancete (fact table):

VAR TI = RELATED(dCentrodeCusto[NOME]) = "INFORMATION TECHNOLOGY"
VAR ValidAccount = NOT(RELATED('dC Account'[Management Accounts]) IN { 
    "Salaries and Wages", 
    "Overtime", 
    "Labor Charges", 
    "Terminations and Compensation", 
    "Bonuses and Bonuses" 
})
VAR InitialExpense = NOT([COMPLEMENT2] = "JOAO DA SILVA")
RETURN TI && ValidAccount && InitialExpense

• fPartida and fpitempartida (hide everything):

FALSE()  // Hides all rows

• ftmovitens and fvalorcado (Use same logic as fAjusteGerencial but adapt column names)

Third and Last Step is to add this to UserSecurity table:

[UserEmail] = USERPRINCIPALNAME()

I hope you find this useful cause it took too much time and if you need any help tell me and sorry for delay reply 🫡❤️

[PedroModa]

@Ahmed-Elfeel 

Thank you very much! I've seen some content about dynamic RLS, but since I've never worked with it before, I still have many questions... One of them is, for example, if this manager can view 25 cost centers, should I create 25 rows in the table for this manager? For example:

manager@powerbi.com | cost center 1

manager@powerbi.com | cost center 2

manager@powerbi.com | cost center 3

And so on?

And in cases where, for example, I wanted to "hide" the information in a column of ONE TABLE for this rule:

manager@powerbi.com | cost center 2

the transactions table, for example, I want to hide the information for supplier 3...

How would I do that? This is very confusing for me. Should I handle this in the table itself, or should I do this more "detailed" handling within Power BI?

[Ahmed-Elfeel]

Hi @PedroModa,

So lets Take questions one by one 😅❤️

First Question

If this manager can view 25 cost centers should you create 25 rows in the table for this manager?

• So Yes Exactly create 25 separate rows exactly like this:

UserPrincipalName	CostCenter
manager@powerbi.com	CC01
manager@powerbi.com	CC02
manager@powerbi.com	CC03
manager@powerbi.com	until reach the max (25)

Second Question

What's the RLS rule for this structure?

• Just a single and simple rule on your security table:

[UserPrincipalName] = USERPRINCIPALNAME()

Third Question

How do I hide a supplier in only ONE table?

• So For this Question there is 2 Approaches you could try (the more comfortable for you)\
• First one is to add restriction columns to your main security table:

UserPrincipalName	CostCenter	RestrictedSupplier
manager@powerbi.com	CC02	Supplier3

And the RLS Rule for this:

AND(
    [CostCenter] IN VALUES(SecurityTable[CostCenter]),
    NOT([Supplier] IN VALUES(SecurityTable[RestrictedSupplier]))
)

• Second one is to create dedicated tables for different restriction types:

UserPrincipalName	RestrictedSupplier
manager@powerbi.com	Supplier3

And the RLS Rule for this Table is:

// Main cost center access
[CostCenter] IN VALUES(UserCostCenterAccess[CostCenter])

// Supplier restrictions  
NOT([Supplier] IN VALUES(UserSupplierRestrictions[RestrictedSupplier]))

Finally

• You should always handle this in the RLS tables (not inside Power BI visuals or complex DAX)

• Power BI RLS is relationship driven (not DAX driven) , So each restriction type must have its own small table

if this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.

[Ahmed-Elfeel]

Hi @PedroModa,

I hope you are doing well today ☺️❤️

So the issue is likely the additive nature of RLS when a user is assigned to multiple roles; your user's membership in 25 roles means Power BI must combine the filters from all of them which can severely impact performance (The OR conditions within each rule further complicate the filtering logic)

So what should you do to solvethis issue?...Here is some approach you can try :

First Approach : (Long term scalability and for large organizations)

• Create a central table linking users to their cost centers (this can be an Excel sheet,database table or SharePoint list that you import)

• Use one RLS role with:

[Username] = USERNAME()

• Relate this table to your data model (filters propagate automatically)

Second Approach (Quick fix)

• Merge 25 roles into 1-3 broader roles

• Replace multiple OR conditions with single IN clause
• Reduce additive filtering overhead

Third Approach

• Combine RLS with page-level filters
• Use summary tables for managerial views
• Implement RLS sparingly

Final Approach (Technical Optimize)

• Convert text codes to integer IDs

• Ensure single direction relationships

• Use imported tables vs direct query

if this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.

[v-priyankata]

Hi @PedroModa 

Thank you for reaching out to the Microsoft Fabric Forum Community.

@amitchandak  Thank you so much for your inputs.

I hope the information provided by users was helpful. If you still have questions, please don't hesitate to reach out to the community.

[amitchandak]

@PedroModa , When you use more than one role, they are treated as OR conditions. Therefore, we need to merge those roles into a single role. This means you must combine the conditions into one role if you want them to function as an AND condition