RLS - Issue with filters

[agrawalkriti]

Hello,

Wondering if anyone has encountered the issue we are having after implementing dynamic RLS. In summary, we have a pretty standard star schema data model (One fact table joining to 6 different dimension tables). One of the dimension has list of Organisation and we need to apply RLS based on this value. To achieve this we have created another bridging table which has OrgID and UserID as one user can have access to multiple Organiations etc.

So the model looks something like

Fact - Dim 1 (many to 1)

Fact - Diim 2 (many to 1)

Fact - Dim 3 (many to 1)

Fact - Dim 4 (many to 1)

Fact - Dim_Org (Many to 1 )

Dim_Org_user_mapping - Dim_Org    (Many to 1 ) - bidirectional filter set on this table.

And we have applied security filter on Dim_org_user_mapping table for RLS to work. Now RLS is workign as expected but the issue we have is that other filters applied to report are dis-regarded.

E.g. if User wants to select  data from fact table where Dim2.attr1 = <value> , with RLS PowerBI still reads all the data from fact and fails with 1mn rows contraint. If I run the report without RLS - then the report returns the data (with the filter). What we have observed is that PBI ignores any filter applied from other dimension table in its native query

Hope someone can explain why this happens? And how to resolve this issue.

[agrawalkriti]

Thanks Yadong for replying. The model works correctly when RLS is not applied, it is only when RLS is taken into consideration (i.e. running as a "user") then the filters stop working. It still produces the right result but the filters are somehow not taken into consideration. I have compared the queries sent to database by PBI on both instances (with RLS and wihtout RLS) and the only difference is in WHERE clause - with RLS it omits the filter condition and uses 1 =1 instead. 

[v-yadongf-msft]

Hi @agrawalkriti ,

If RLS produces unexpected results, check for the following issues:

• Incorrect relationships exist between model tables, in terms of column mappings and filter directions.
• The Apply security filter in both directions relationship property isn't correctly set. 
• Tables contain no data.
• Incorrect values are loaded into tables.
• The user is mapped to multiple roles.
• The model includes aggregation tables, and RLS rules don't consistently filter aggregations and details. 

For more information, please refer to: Row-level security (RLS) guidance in Power BI Desktop - Power BI | Microsoft Docs

Please check relationships between model tables and try again.

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.