Hi, I have a data model with the following:
Fact table "visits"
Department dimension
Provider dimension
I'm implementing dynamic row level security, where report users can below to one of two different roles:
"Providers" can only see their own personal data, not the data of other providers
"Managers" can see all the data for their deparment, including provider level data, but can't see other departments
I have this working, but now have been asked to add one additional requirement. For "providers" - they should still see their own data, but they should also be able to see the data in their entire department (i.e. department totals should still show up so they can compare themselves to their department averages...they just still shouldn't be able to see any OTHER providers data)
I'm not sure if this is possible to do or not...I'm struggling to understand if / how to implement "limit the provider dimension to just the one provider...unless looking at the whole department, in which case don't limit the providers". I could start down a rabbit hole of using IsFiltered, IsInScope, etc. to concoct some DAX formulas....and I've thought of creating a second fact table that only connects to department and not to provider.
None of these seem simple or elagant, and I'm concerned about botching up the security if I get too fancy. Has anyone done something like this in the past?
Thanks,
Scott
