RLS set value - undesired behavior

[Credo]

Hi 

I thought I had RLS figured out but I am just puzzled here 

I have some sales connected to a Product dimension table with a Category column (A, B, C etc)

I have created a role with the only filter being applied to the Category column where [value] = "C"

Without the role active, here is what I see on a new report page

also here is a snap of the page level filter for the Categories

 

If I apply the role I see this 

As you can see the table on the left got filtered correctly and is only showing C, however, the total value on the table as well the visual on the right remains unfiltered. 

In addition, the page level filter now only lists C as an available option and it remains unchecked. If I do check it then everything filters correctly. 

After some tinkering I discovered that if I manually select each category on the page level filter before applying the role

than when I do apply the role it works as intended

Can someone explain to me what is going on here? Am I doing something wrong?

[ssugar]

I'm not able to reproduce your issue.

I've created a pbix file with the same data as yours, but mine is filtering correctly when applying RLS:

https://github.com/ssugar/PowerBICommunity/raw/master/community-sol-561821.pbix

Could you make sure your running the latest version of Power BI Desktop?

Version: 2.63.3272.40461 64-bit (October 2018)

[Credo]

Yes I am running the same version 2.63.3272.40461 64-bit (Октябрь 2018)

[ssugar]

Does the pbix file I linked to in my earlier post work properly for you with RLS?  Is there any difference between your data or your RLS setup and the one I created in the pbix file?

[Credo]

I have reproduced the issue (not sure how to attach a file)

It occurs if there is a many to many relationship with a bridge in between 

[ssugar]

A few things:

1. Which Category field are you using in RLS?  The one from the Sales table, or the one in the DimProduct table?  I'm guessing the one from the DimProduct table?

2. Looks like your relationship between the Sales table and the Product key bridge is set for Cross Filter Direction = Single, does anything change if you change that to Both?

[Credo]

1. Yes I am using the one from DimProduct 

2. No even if both relationships are set at cross filter = both, the issue persists. Also I would not be able to do that in the real file anyways since that would have created ambiguity. 

[ssugar]

Could you share some sample data from your sales and your dimproduct tables?  Something we could copy/paste into Power BI desktop would be easiest to work with.  I copy/pasted the below data from excel into the forum post editor

A	1	10
B	2	20
C	3	30

[Credo · Accepted solution]

I was able to partially resolve the issue by activating "apply security filter in both directions" between a bridge and dim table 

That eliminates the issue for the sample file however it looks like I am out of luck for my work file because I have multiple fact tables connected through multiple bridge tables to a single dim table. I can toggle the security filter checkbox for one of the bridge tables which resolves the issue for that tables and connected data, however, it won't let me toggle security filter for all 4 bridge tables.

Edit:

Alternative workaround - add report level filter for the values that will be filtered by your role.

Don't check the "select all" box but instead manually tick each box for every value.

[ssugar]

One other question for you: Which table does the Category field come from for the Table and the Page level filter in your initial post?

[Credo]

In my initial post I used screenshots from my work file.

After you mentioned that you could not reproduce the issue, I made a mock-up file to reproduce it myself on a smaller scale and we've been discussing it since.

The only difference is that in the mockup there is 1 fact 1 bridge 1 dimension table. 

In the work file there 4 fact 4 bridge 1 dimension table.

The security filter setting can only be activated for one of the bridge tables. 

I tried creating another bridge table that linked to the whole dimension table like this

F - B -

F - B -

F - B - Dim - Bridge (with security)

F - B -

and applying the rls vallue setting as well as the security filter there. In this case the issue persists and the security filter does absolutely nothing. 

The only alternative is add report level filter and manually check each box for the values there. When the role is applied only the relevant value will kick in the report level filter. Does not work if "all" is selected. 

[Credo]

Here is the file

https://we.tl/t-vajaseRhAU

Here is the data (random stuff i typed to reproduce the problem)

Fact sales 

Category    Sales                                                                      Product key

A	124	1
A	4387	2
A	12376	1
B	8574	3
B	1231325	3
B	78456	4
B	666	4
B	11	4
C	3	5
C	556	5
C	9899	5
C	251	6
C	57645	6

Bridge table 

Product Key

1

2

3

4

5

6

DimProduct

Category         Product Name                                                             Product Key            Duplicate

A	Name 1	1	1
A	Name 2	2	1
B	Name 3	3	1
B	Name 4	4	1
C	Name 5	5	1
C	Name 6	6	1
A	Name 1	1	2
A	Name 2	2	2
B	Name 3	3	2
B	Name 4	4	2
C	Name 5	5	2
C	Name 6	6	2