cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
MohanV Established Member
Established Member

RLS for different levels and different categories

Hello All,

 

I have a table where few of the employees are given at such different level.

 

Screen Shot 2019-04-10 at 4.09.55 PM.png

 

--> A employee have an access of SBU1,SBU2,SBU3 for all PRACTICES of only USA, UK region.

--> B employee have an access of SBU1 for Practice1 of only INDIAN region.

--> C employee have an access of all the sbu's for all PRACTICES of only CANADA region.

 

And i have a table of masterdata, where it contains the SBU, PRACTICE, REGION as columns along with EmpID,userName.

 

EmpName  SBU   Practice     Region          Salery

eSBU1Practice1INDIA100
fSBU1Practice1USA200
gSBU2Practice2UK300
hSBU2Practice3CANADA400
iSBU3Practice4INDIA500
jSBU3Practice4USA600

 

Now whenever a1 logs into our powerbi report, he able to see data for SBU1,SBU2,SBU3 for all PRACTICES of only USA, UK region.

Like wise we have to show the data for rest of the employee.

 

Is it possible in powerbi.

 

Any help or suggestions will be much apprciated.

 

Regards,

Mohan V

 

1 ACCEPTED SOLUTION

Accepted Solutions
natelpeterson New Contributor
New Contributor

Re: RLS for different levels and different categories

@MohanV - 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

9 REPLIES 9
natelpeterson New Contributor
New Contributor

Re: RLS for different levels and different categories

Hi @MohanV ,

 

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

 

guiggs Frequent Visitor
Frequent Visitor

Re: RLS for different levels and different categories

Hi @MohanV !

 

Yes, you can do that in PowerBI Desktop and PowerBI Service.

 

In PowerBI Desktop you create the roles that you want, the 3 that you described.

After you publish your report to PowerBI Service, you assign the roles to the people that have access to the report by the email that they use to enter the Service.

MohanV Established Member
Established Member

Re: RLS for different levels and different categories

Thanks for the reply @natelpeterson .

 

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

MohanV Established Member
Established Member

Re: RLS for different levels and different categories

@guiggs  thanks for the reply.

 

But i was looking for a help, that, how it can be done in power bi desktop using dax level.

natelpeterson New Contributor
New Contributor

Re: RLS for different levels and different categories

@MohanV - 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

vmakhija Member
Member

Re: RLS for different levels and different categories

@MohanV 

See below DAX. It might help you. It assumes that user table can have comma separated values, similar to how you want.

 

Var Businesses = CALCULATETABLE(VALUES(UserMaster[Business]), UserMaster[ID]=USERPRINCIPALNAME())
Var Regions = CALCULATETABLE(VALUES(UserMaster[Region]), UserMaster[ID]=USERPRINCIPALNAME())
Var Countries = CALCULATETABLE(VALUES(UserMaster[Country]), UserMaster[ID]=USERPRINCIPALNAME())

Return
AND(
OR("All" IN Businesses,
CONTAINSSTRING(Businesses,Sales[Business])),
AND(
OR("All" IN Regions,
CONTAINSSTRING(Regions,Sales[Region])),
OR("All" IN Countries,
CONTAINSSTRING(Countries,Sales[Country]))
)
)

 

You will have to change the field names as per your case, but above is to give you an idea.

 

Regards

MohanV Established Member
Established Member

Re: RLS for different levels and different categories

Thanks for the reply @vmakhija .

 

Few Questions..

1:- where do i have to write this dax, In Sales or UserMaster or in Manager Roles --> Roles --> Table Filter DAX Expression.

 

2:-  I did tried to create a column in my sales table, usermaster table and aswell as in Manager Roles --> Roles --> Table Filter DAX Expression using the same dax, but HERE i am using Power Bi Report Server Desktop Version: (January, 2019). So the CONTAINSSTRING function is not available i guess. its not recognized, giving error. Could you please share the alternative function for the same.

 

3:- Do i have to create relation between usermaster and sales table??

 

Please help,

Regards,

Mohan V.

 

vmakhija Member
Member

Re: RLS for different levels and different categories

Hi @MohanV 

1. You need to write this expression under Manage Roles --> Roles --> Table Filter DAX Expression for the table "Sales".

2. Try to upgrade to latest version. If not, you can try using "SEARCH" function.

3. Relation between UserMaster and Sales is not necessary for RLS to work.

 

Regards

Community Support Team
Community Support Team

Re: RLS for different levels and different categories

Hi @MohanV ,

Have you solved your problem?

If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If you still need help, please feel free to ask.

Best  Regards,

Cherry

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.