Filter based on username without using row level security

[cv77]

Hello,

- I have a table with a username and a geographic area, and users are allowed to see only particular geographic areas as well as report visuals which contain rolled up (aggregated) data for all areas, and more than one user may be allowed to see a particular area
- when we use powerBI's RLS the rolled up data is restricted as well, so we can't use RLS
- when users access the powerbi reports we can get their AD username and would like to restrict the data using the user table which contains this AD username and IDs of allowed geographic regions
- how can we implement this, given that we can't filter on a page using a measure? I tried putting the UPN in a measure and then creating a custom column in the usertable which contains a 1 if the UPN matches the username in the table and a 0 if it does not, but I can't reference a measure which contains Username() in a column either

Thanks.

[lbendlin · Accepted solution]

that, or providing different apps to different audiences.

[cv77]

Thank you for your clarifications.  Providing different apps to different audiences isn't an option for us so I will look at changing how the rolled up data is stored.  

[lbendlin]

You're describing how RLS works. "Stop fighting the API"

[cv77]

So redo the data model taking the aggregated data outside the tables filtered by RLS.  That is the only solution?  

[lbendlin]

How would that be different from RLS? 

[cv77]

My thinking is to create a table which is joined to the usertable to get the allowed regions, and pass the allowed regions to the rest of the dataset without using RLS.  The main dataset is not joined to the table filterd by RLS.  Could be my thinking is wrong though.  

If anyone has ideas on how to do this would be apreciated.  

[amitchandak]

@cv77 , You can not refer a measure, UPN or a slicer value in a new column

[cv77]

Ok, thanks for confirming that.  Appreciate the response.  

I am thinking to try this

- create another table with the usernames and regions, which is not joined to the rest of the dataset

- dynamicaly read the selected values from it, using something like SELECTEDVALUES()

- somehow (?) filter the main region table using those values, which in turn filters the rest of the dataset

I haven't tried this, this is just what I am thinking to try next as am running out of ideas.

-