Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Anonymous
Not applicable

@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

View solution in original post

9 REPLIES 9
vmakhija
Post Prodigy
Post Prodigy

@Anonymous 

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

Anonymous
Not applicable

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.

 

Hi @Anonymous ,

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.

Hi @Anonymous 

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

guiggs
Helper II
Helper II

Hi @Anonymous !

 

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.

Anonymous
Not applicable

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

Anonymous
Not applicable

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

 

Anonymous
Not applicable

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
Not applicable

@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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.