Power BI – Dynamic Row Level Security

[Thejeswar]

I have a requirement to provide access to several users based on multiple columns of a table., on which I am currently facing issue. Let me explain it in a much detail manner

For example, My Hierarchy level is Product Category, Product Sub-category and Product Name.

Suppose a user needs access to few product categories, few sub-categories and few product names at the same time, Is there any way I can implement this in Power BI? 

When I went through the Power BI RLS, I was able to see that it is possible to implement security for any one level of hierarchy.

In many other tools, there are ways to implement security based on the User Employee Number but in Power BI as far as I have seen, I can see that the Access table is being joined with the model based on Id column (Assuming that the Access control table has two columns (Id and User Name)

Is there anyway, I can use comma separated values for filtering insidde the report i.e. Using IN statements in filters instead of OR statments? This Access table might have several other columns on which the Access needs to be implemented

For reference, I have attached my existing model and my expected model

Note: Implementing access to each users row by row for each product would not be a good solution for me, as I have more than 700 products under consideration with more than 100 users having access to all products. In such case, this alone would require me to input 70000+ columns in my access table. Not to forget, other users who will have access to few products each.

Existing Data Model:

 Expected Data Model:

Any help is highly appreciated!!! 

[Anonymous]

You should be able to allocate a person to multiple categories.

I'd set up security views that allow access to each descreet aspect, then assign a person to each category.

For example, if you only wanted someone to have access to "Apples" and "Bananas", but also access to the category "Candy".  I'd create a group that has access to the apples and bananas, then a seperate group for candy.  You can then add the person to both groups, giving them access to only those 2 sets.

[Thejeswar]

I already have  a similar setup being used for controlling access. 

I have separate tables for the three columns (say Product Category, Subcategory and Product Name) and the User Name being added to those tables for controlling the RLS. This is then joined with the data set.

Over a period of time as new items get added based on which further access control is required, this approach might become difficult for maintenance