Forum Discussion
RLS Exclusionary Role with NOT PATHCONTAINS() Not Filtering Data
Hi jbauti10 ,
The issue turned out not to be related to Power BI Embedded, but rather to how NOT PATHCONTAINS() behaves inside Row Level Security (RLS). When the identity string parsing returns a blank or unexpected value, PATHCONTAINS() evaluates to FALSE, and wrapping it with NOT makes the overall expression TRUE for every row. As a result, no records get filtered out, which is why the exclusionary role appeared to not work.
To fix this, the exclusion logic was rewritten using a more reliable pattern. Instead of relying on NOT PATHCONTAINS(), the pipe delimited values from USERPRINCIPALNAME() were converted into a table, and the filter was applied using NOT (Column IN Table Variable). This approach avoids the evaluation issue and works consistently within RLS. After applying this logic separately to both the Challenge and Community tables under the same role, and properly activating the role using View As - Other user in Power BI Desktop, the filtering worked as expected. With the test identity CHALLENGES=3696|3326;;COMMUNITIES=625;;EVALUATOR=135543, the Challenge table correctly excluded 3696 and 3326 (showing 3324, 3327, 5391, 51418), and the Community table correctly excluded 625 (showing 999999). This confirms that the behavior was due to how NOT PATHCONTAINS() evaluates in RLS, not a defect in embedding or the RLS engine.
Please find the attached .pbix file for your reference.
Hi v-menakakota ,
Please find the sample report with the sample data in the following link.
Sample Data Provided:
The .pbix file contains a semantic model with the following sample data:
- Challenge IDs in the dataset: 3696, 3326, 3324, 51418, 3327, 5391
- Community IDs in the dataset: 625, 999999
RLS Configuration:
I have two exclusionary roles configured:
- Challenge Access (Exclusionary) - applied to Challenge Table
- Community Access (Exclusionary) - applied to Community Table
Both roles parse a structured string passed via USERPRINCIPALNAME() in the format:
CHALLENGES=<ids>;;COMMUNITIES=<ids>;;EVALUATOR=<id>Test Scenario:
When embedding the report with this identity username:
CHALLENGES=3696|3326;;COMMUNITIES=625;;EVALUATOR=135543Expected Outcome:
- Challenge Table should exclude challenge_ids 3696 and 3326, showing only: 3324, 51418, 3327, 5391
- Community Table should exclude community_id 625, showing only: 999999
- Any related tables (like idea_table) should be filtered accordingly through the relationships
Actual Outcome:
- Challenge Table shows ALL 6 challenge IDs: 3696, 3326, 3324, 51418, 3327, 5391 (no filtering occurs)
- Community Table shows BOTH community IDs: 625, 999999 (no filtering occurs)
- The exclusionary roles are not filtering out any data
Note: The inclusion-based role works perfectly, only showing the IDs specified in the list. The issue only occurs when using NOT PATHCONTAINS() for exclusionary filtering.
Hi jbauti10 ,
The issue turned out not to be related to Power BI Embedded, but rather to how NOT PATHCONTAINS() behaves inside Row Level Security (RLS). When the identity string parsing returns a blank or unexpected value, PATHCONTAINS() evaluates to FALSE, and wrapping it with NOT makes the overall expression TRUE for every row. As a result, no records get filtered out, which is why the exclusionary role appeared to not work.
To fix this, the exclusion logic was rewritten using a more reliable pattern. Instead of relying on NOT PATHCONTAINS(), the pipe delimited values from USERPRINCIPALNAME() were converted into a table, and the filter was applied using NOT (Column IN Table Variable). This approach avoids the evaluation issue and works consistently within RLS. After applying this logic separately to both the Challenge and Community tables under the same role, and properly activating the role using View As - Other user in Power BI Desktop, the filtering worked as expected. With the test identity CHALLENGES=3696|3326;;COMMUNITIES=625;;EVALUATOR=135543, the Challenge table correctly excluded 3696 and 3326 (showing 3324, 3327, 5391, 51418), and the Community table correctly excluded 625 (showing 999999). This confirms that the behavior was due to how NOT PATHCONTAINS() evaluates in RLS, not a defect in embedding or the RLS engine.
Please find the attached .pbix file for your reference.