RLS not working for only one table

[nok]

Hi!
I created RLS for several tables in my report, all following the same code (except for the last part where I change the respective table reference). This is the code:

var _User = USERPRINCIPALNAME()
var _Access = 
	MAXX(FILTER('_UserManagement',
        '_UserManagement'[Email] = _User),
        '_UserManagement'[Type])

var _Permissions =
	CALCULATETABLE(
	VALUES('_UserManagement'[RLS_AccessCode]),
	KEEPFILTERS('_UserManagement'[Email] = _User))
			
RETURN
   IF(
	_Access = "Admin",
	TRUE(),
	'Table_E'[RLS_AccessCode] IN _Permissions
)

For all tables, the RLS is working perfectly, but for one specific table (Table_E), it doesn't work at all, and no data even appears when I try to view it as "user1@email.com", wich is an Admin and should have access to all data. My table that helps with access management (_UserManagement) is this one:

Email	Type	NTC	Company code	RLS_AccessCode
user1@email.com	Admin	ALL	ALL	ALL_ALL
user2@email.com	User	Europe	ITTX	Europe_ITTX

Even when I delete all the RLS code from this table and leave only the TRUE() function, it doesn't work and no data from this table appears. What am I doing wrong?

[v-tsaipranay]

Hi @nok ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank you.

[nok · Accepted solution]

I managed to solve this issue by doing a workaround because for some reason RLS doesn't seem to work with bidirectional many-to-many relationships. That was the problem. So to "solve" this, I needed to create a bridge table with two columns: one for the distinct ID that would relate to all the other tables, and another for the RLS Code. Then I applied RLS only to this bridge table, and it worked.

[v-tsaipranay]

Hi @nok ,

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

Thank you.

[HarishKM]

@nok Hey,
I will use below method and dax for the RLS.

1. Ensure that Table_E is properly connected to _UserManagement in your data model. If the relationship is missing or directional filtering is incorrect, it might prevent the filter from working.
2. Check any privacy settings or configurations in Power BI that might be restricting access to Table_E. Verify Data Type Matching: Confirm that the column types in Table_E match those expected by the RLS code and _UserManagement. Sometimes mismatched data types can lead to issues.
3. Ensure there are no conflicting RLS roles applied to Table_E that might be negating the effect of your current code.  Verify the syntax and logic in the specific RLS code you've applied to Table_E.
4. I can se that you have already tried using TRUE(), which bypasses the filter completely, so the issue may lie elsewhere. Re-import or Refresh the Data: Sometimes simply refreshing or re-importing the data can solve strange visibility issues.
   I will use below dax for this
5. case.VAR _User = USERPRINCIPALNAME()
   VAR _Access = CALCULATE( MAXX( FILTER( _UserManagement, _UserManagement[Email] = _User ), _UserManagement[Type] ) )
   VAR _Permissions = CALCULATETABLE( VALUES(_UserManagement[RLS_AccessCode]), _UserManagement[Email] = _User )
   RETURN
   IF( _Access = "Admin", TRUE(), Table_E[RLS_AccessCode] IN _Permissions )

Thanks

Harish KM

If these steps help resolve your issue, your acknowledgment would be greatly appreciated.

[v-tsaipranay]

Hi @nok ,

Thank you for reaching out to the Microsoft fabric community forum.

Based on your explaination, the problem appears to be structural rather than related to RLS, since Table_E displays no data even when using a simple RETURN TRUE() rule. Because Table_E is linked to four child tables through bidirectional many-to-many relationships, it's possible that filters from these child tables are unintentionally blocking rows in Table_E. To check this, try temporarily removing or switching those relationships to single-direction, then apply the TRUE() RLS rule again to see if the data appears. If it does, this suggests the issue is with the relationship setup rather than the RLS code.

To achieve more stability and scalability, it's recommended to restructure your model so that _UserManagement serves as a dedicated dimension or bridge table, establishing relationships with your target tables. Applying RLS at this level can help prevent issues that may arise from relying only on DAX-based virtual joins, which can be inconsistent in more complex models.

Hope this helps. Please reach out for further assistance.

Thank you.

[nok]

Hi, @d_m_LNK  and @DataVitalizer 
I ran a test here, and noticed that the problem seems to be in the relationship between the Table_E and its children. Table_E is a parent table that connects to four other tables that are its children, in a many to many relationship (*:*). This RLS works perfectly on the child tables, but it doesn't work on the parent table. And I really don't understand why, because the _UserManagement table, which is the table that manages access, has no relationship with any table in my report.

[d_m_LNK]

That is interesting, are those relationships bi-directional to the child tables? Maybe that has something to do with it.

I think it would be to your benefit to figure out to how to relate your _UserManagement table to your models and have it do the RLS but that's more based on my experience

[nok]

Yes, Table_E relates to its four child tables bidirectionally. What I find very strange is that, even when I leave only the TRUE() function in RLS, Table_E still doesn't display any data, but the four child tables display data. Very strange.
And _UserMangement table has no relationship with other tables.

[d_m_LNK]

A couple things to check:

-Relationships between TableE and the _UserManagement Table (If applicable)

-make sure you have this rule applied to the right table

Another question, is the second half of your IF statement supposed to be the start of another IF?  Not sure the purpose of that second statement without more context.

[nok]

Hi @d_m_LNK . Thanks for the reply!

I don't have any relationship between Table_E and _UserManagement. In fact, _UserManagement doesn't have a relationship with any table in my report.

All "communication" between _UserManagement and my other tables is done directly in the RLS code.

And the purpose of the second part of my IF is that if a user is not of the Admin type, the report should only show him the data where the value in RLS_AccessCode column is the same as the table created in the _Permissions variable.

[d_m_LNK]

So you might consider nesting another If statement in that so it returns true but that might not be the issue.  You could also converting the IF statement to a Switch(TRUE()) statement that way you can control what each check will return.

The way I've applied RLS in my org was to create dimension tables with the folks that need access and  making sure there is a correlating ID on the fact table of the model to relate to.  That way you apply the RLS to the dimensions and that filters out the rows in the fact that meet the criteria you need.  

In your case, your user management table could be a dimension as long as you could categorize whatever fact you are using by access code or create a bridge table to translate what that filter would ultimately look like.  

[DataVitalizer]

Hi @nok 

Your RLS looks mostly good, from my perspective here’s why Table_E might not be showing data:

1. Double-check that the RLS_AccessCode column is exactly right in Table_E same name, type, and values that match _UserManagement.
2. Make sure Table_E has the right links or data to work with the filters.
3. Try this quick test to see if Admin access works by itself:

var _User = USERPRINCIPALNAME()
var _Access = MAXX(FILTER('_UserManagement', '_UserManagement'[Email] = _User), '_UserManagement'[Type])
RETURN IF(_Access = "Admin", TRUE(), FALSE())

If you get data now as Admin, then the issue is with the permission matching.

Did it work? 👍 A kudos would be appreciated
🟨 Mark it as a solution to help spread knowledge 💡

🟩 Follow me on LinkedIn

[nok]

Hi, @DataVitalizer . Thanks for the reply!

1. I checked everything, and the RLS_AccessCode column is the same as all the others.
2. Table_E (like all the other tables in my report) doesn't have a direct relationship with _UserManagement. The "connection" between them is only made in the RLS code. Is this a problem?
3. I used this code, but the data still doesn't appear for Table_E9. Even when I delete all the RLS code from this table and leave only the TRUE() function, it doesn't work, and no data from this table appears. Very weird.

[DataVitalizer]

@nokI suggest temporarily create a new table visual with just Table_E[PrimaryKey] or any non-filtered column and:

1. Remove all filters, all visuals, all slicers.
2. Use no measures, just raw data.
3. Apply TRUE() in RLS role.
4. Preview as Admin.

If that doesn't work then the table is not showing because of something structural in the model, not because of RLS filtering logic.

Did it work? 👍 A kudos would be appreciated
🟨 Mark it as a solution to help spread knowledge 💡

🟩 Follow me on LinkedIn