Forum Discussion
RLS set value - undesired behavior
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.
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?
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.
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 Here is the file
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 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.
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?