Hi,
The logic is really escaping me on how I can deal with this issue and I can't find anything that is exactly the same so I'm hoping someone can help!
I have a list of users for my Power BI report. Some of them will need access to All rows in my fact table, some will need to have a subset, based on my [department] field, held both in my user table and in my fact table. I need it to work using dynamic row level security.
I've tried separating out the users into two tables and then creating a join from the "Full Access User" table to the Fact Table on a field I have just called "All" in both tables. I then link the "Subset User" table to the Fact Table on the "department" field. That does cause a many to many relationship though in both cases which limits what I can do.
When I come to look at the Roles to implement the security - using two user tables doesn't exactly work as its an AND between two tables and it just shows no data for any user.
I tried this separation of tables as I read that USERELATIONSHIP type logic wouldn't work with RLS? And the only solution I can think is to create two different joins to the fact table? But I could have one table with all the users if that's a better solution and it could work somehow.
Any pointers would be very very much appreciated! I think my main issue is how the tables join together, which seems simple but isn't?
