RLS that filters both fact and dimension tables

[Anonymous]

Hi, I am having trouble setting up RLS which wil filter both measures and dimensions based on user login.

This is simple example of problem i am facing.
I have both direction relationsips between my tables. This was user requirement so if they filter from one dimension, they are expecting to se only relevent dimension values in other dimenison.

Now in this simple example i have made it that user Max is having only Sales in France and for Private Customer Type (You can se it in fact table), but once I filter using RLS on Max my dimension tables are not filtered. You can see Dimnesion slicers on the right.

Desired behaviour in this case would be that all dimneisons are prefiltered with relevant values. So Max is having France and Provate as options to choose from.

I already tried with "apply security filter in both directions" but there is restriction so this only works on one connection per table. So this is not solution in my case,  becasue my production dataset has 23 dimension tables. I have also seen solutions with bridge tables, which could work, but at the end i will end up with snowflake instead of star schema, and 23 tables more in my dataset, so i would try to aviod that and seek for simple and easy to maintain solution.
I would appriciate any idea or help here 🙂
dataset can be donwloaded here

[sturlaws]

Hi @Anonymous,

if it is acceptable to solve this in the report itself, there is a new feature(from the june release?), which allows you to filter slicers by using measures. Using an appropriate measure, either sales or a specificly made measure for this purpose, the slicers will only show the dimension values which have value in the fact table.

[Anonymous · Accepted solution]

Hi @sturlaws , thank you for your idea.

This would be simple solution for report level, but i have solved it on dataset level since I am going to use this dataset of rselfservice as well.

I have created RLS on all dimension tables, with logic so check are there related values in fact table.

If there are any i am showing dimension value, if not i am hidding.