How to filter dimension based on Dynamic RLS applied to another dimension

[sumanthkakarla]

I have this below data model where i have implemented Dynamic RLS, however the three visual filters are getting populated from Fact table which is taking lot of time to load the filters, to get rid of this kind of approach i have introduced three new dim tables where i am deriving these using fact table to load the filters faster, however the issue that i am experiencing here is, if i pass a user to my Dynamic RLS the user is able to see all the healthsystems ratherthan the ones that are assigned to him and its similar problem to the rest of the two filters that i created ,

Here is how i created a new 4 dim tables based on fact tables, 

Health System Dimension = DISTINCT('TAT Only'[HEALTH_SYSTEM])

Facility = DISTINCT('TAT Only'[CLIENT_FACILITY_ACCOUNT_NAME])

TEST_ID = DISTINCT('TAT Only'[TEST_ID_NAME])

so i pass username as peter.jamison@google.com, where he has access to only two helath systems, 10 client faciclity account names and 20 Test ID names,  he is suppose to see only those values in my visual filters, rahter he is seeing the enire data, how can i restrict these three visual filters highlighted in yellow to show only data assigned to him when i click on that drop down filters

Any help is greatly appriciated.

Tried applying Cross filter direction to Both did not help, tried to apply "Apply sercurity filters in both dirctions" is only working for one relationship and for the rest of the relathionships i am getting this error. 

[sumanthkakarla]

My issue got resolved by applying DAX to each and every dimension, i did not find any other solution othe than this at this point in time.

[v-hashadapu]

Hi @sumanthkakarla , Please let us know if your issue is solved. If it is, consider marking the answer that helped 'Accept as Solution', so others with similar queries can find it easily. If not, please share the details.
Thank you.

[v-hashadapu · Accepted solution]

Hi @sumanthkakarla , Please let us know if your issue is solved. If it is, consider marking the answer that helped 'Accept as Solution', so others with similar queries can find it easily. If not, please share the details.
Thank you.

[lbendlin]

It is not clear which table has the USERPRINCIPALNAME() RLS rule applied, but that table must be able to control the parts of the data model where you want RLS to apply.  If you have bidirectional links then RLS will be ineffective unless it is applied on both sides.

[sumanthkakarla]

Thank you for your replu @lbendlin , here is the Dynamic RLS that i have applied to one of the bridge table.

if the logged in user has access to 'ALL' accounts then i sould not apply any filters and show all the data to the logged in user if not i should display data only access to that particular user.

[lbendlin]

That looks ok (aside from the unfortunate table name. The actual bridge table is client_lookup). 

With this setup in place, what is the issue you are observing?

[sumanthkakarla]

the issue that i am observing here is, the filters are not working as expcted, as shown in the below image ( highlighted in yellow), i have three visual filter, HEalth SYstem, Client Facility Account Name and Test ID Name , when i apply Dynamic RLS i expect the Health Ssystem should show only assigned health system names which are assigned to that user instead it is showing all the health system names in that dim table, so the fact is not filtering the dim table, similar kind of case with other visual filters, what am i doing wrong here and how to fix it

[lbendlin]

Change the health system dimension join to single direction, dimension to fact.

[sumanthkakarla]

that did not help either. still the logged in user seems all health sytem names which he dont have access.  

[lbendlin]

make sure you are not testing with a user who has workspace access above Viewer.  RLS is ineffective for these users.

[sumanthkakarla]

I am testing with users who dont have access to powerbi at all where users exist in the account map table.

[v-hashadapu]

Hi @sumanthkakarla , Thank you for reaching out to the Microsoft Community Forum.

The main issue here seems to be that the slicers (visual filters) are being populated from dimension tables that aren’t affected by Row-Level Security (RLS), even though the fact table is. This is expected behavior in Power BI. By default, RLS doesn't automatically propagate to dimension tables unless you specifically design it to. To make RLS work properly across both your fact and dimension tables, so that slicers only show relevant values, you’ll need to apply RLS logic directly on the dimension tables.

Assuming you have a UserAccess table that maps each USERNAME() to the values they’re allowed to see, create relationships between UserAccess and your three dimension tables: UserAccess --> HealthSystemDim  and so on. Apply the [Username] = USERNAME() RLS rule to the UserAccess table. This ensures each user only accesses rows that match their login.

Next, apply RLS filters directly on each dimension table so that only relevant entries appear in slicers. For example, on HealthSystemDim, use:

[HEALTH_SYSTEM] IN 
CALCULATETABLE(
    VALUES('UserAccess'[HEALTH_SYSTEM]),
    'UserAccess'[Username] = USERNAME()
)

If this helped solve the issue, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details, always happy to help.
Thank you.

[sumanthkakarla]

Thank you @v-hashadapu 

I can't create a relationship between user access table and the dimension tables based on username as the dimension tables will have only relevant data and no username exists in any of those dimensions.

To overcome this, i have created a dax formula on all those newly created dims and its working as expected, i have to see the performance though.

[v-hashadapu]

Hi @sumanthkakarla , Thanks for the update. Please do let us know, how it is working and if it is performing without any issues, please mark the helpful answer 'Accept as Solution', so others with similar issues may find it easily. Thank you.