Forum Discussion
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
Hi Anonymous ,
If the master table is used as a single dimension, that should work. You could apply row-level security to the master table.
If the master table is related to multiple dimensions, it could cause a problem with multiple paths between 2 tables - the Master table and the Fact table.
Cheers!
Nathan
Thanks for the reply Anonymous .
Sorry i couldnt understood about the dimentions.
I have only two tables as of now. One is the useraccesstable where it contains all the users and their level of access for sbu, practice, and region.
and i have another table, as master data table, where is contains all the employees salery data along with these sbu, practice and region columns.
So, is it possible to filter the master table, based on login user, and the level of access that user have, from different categories as i mentioned above.
regards,
mohan v
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