RLS for different levels and different categories

Anonymous - OK, I see the requirement more clearly now.

You need to modify the User Access table so that it has a row for each combination that the user can access.

In Power Query:

1. Create a new table with the distinct list of each of your values (SBU, practice, Region) and add another column with the value ALL in every row. 

Now, do the following steps for each column (SBU, Practice and Region):

2. Split by delimiter. This will create separate columns for each of the values in your original column. Use advanced options to ensure that it will split into plenty of columns - Enough to hold the maximum number of values.

3. Select all columns except the new columns.

4. Unpivot other columns - this will make a new row for each of the values in the original column.

5. Remove the Attribute column.

6. Rename the Value column to the original column name.

7. Merge (left outer join) to the relevant distinct table, to join rows that contain ALL.

8. Expand the table

9. Create a new Custom Column that checks for ALL in the original column, like this: if [SBU] = "ALL", [expanded col] else [SBU]

10. Remove the 2 columns that go into the single Custom Column

Now you have a table that has multiple rows per user. You need a way to join to the other table.

11. On both tables, Create a new Custom Column to concatenate the 3 values.

Back in Power BI Desktop

12. Create a relationship between the 2 tables. (It may be best to create a Calculated Table that is the UNION of the distinct key values from both tables, and then create a relationship with each table (bi-directional for the security table)

13. Apply RLS

Cheers!

Nathan