ALL except measure not working when RLS applied

DAX
1

Hello Team,

I have applied RLS based on the username and also created 2 measures

  1. Core Measure
  2. Core Measure with AllExcept

image
image697×361 5.83 KB

For example if I filter Data using slicer with username - Jake@gmail.com my data is filtered like the below (Screenshot 1) which is correct

image

But now I remove the filter “Jake@gmail.com” from slicer which I selected above and want to test the RLS, So I select “Jake@gmail.com” from the view roles but then my data will not show like the above screenshot, the ALLexcept logic is not working

image

Can I know if the RLS does not consider the ALLexcept which is injected ?

Any guidance or inputs is highly appreciated.

Apologies, I couldn’t share the PBIX file since the data is huge and contains official information

Thanks
Dharma

2

Bumping this post for more visibility from our experts and users.

3

Hi @Dharma - I have tested it and getting same view as yours. Sending to our experts @BrianJ @AntrikshSharma for more clarity.

Attached the sample PBIX file.

UBG To Power BI - Model.pbix (715.8 KB)

Thanks
Ankit J

4

@ankit @Dharma Everything is working as expected.

When you filter the slicer you are not getting the correct result, as ALLEXCEPT ignore the filter on slicer. ALLEXCEPT has Sales which expands to RLS Table as well, so by the concept of Table Expansion the filter coming from RLS Table is also removed. So technically the numbers are not filtered/incorrect.

When you use the same ID in the Role it filters the model no matter what, the reason being RLS is more restrictive than the code, think of it like this - RLS is applied when you open the report and connect to the dataset, the code is executed in a context where RLS is already applied. So your data is already filtered for Jake.

1 Like
5

Hi @Dharma - As explained by Antriksh it is working as expected. So, please close the post.

Thanks
Ankit J