Manage Roles - set row security level help

[Randalnik]

Hello everyone,

I`m looking for an advice about security in Power BI. I have read a lot about the DAX and Manage Roles but I`m still not able to set it corectly.

I would like to ask, where I`m supposed to set the setting - in Power BI Desktop or in online Analysis. The story below:

The main table consicts of branches, an amount of orders and sum of sales. Each branch has 1 manager, for example:
Branch_Nbr: 12
Manager: Tom
Order: 25
Sales: 1500$

I would like to allow Tom to see just his branch number 12 and its numbers.

Theres about 50 branches. Do i have to set for each one the setting?

Thanks, regards

Jacob

[Drors · Accepted solution]

You can create a dynamic RLS with the function USERNAME() or USERPRINCIPALNAME(), that function will return the power bi user email, so you will have to create a table with all users email like TOM@EMAIL.com        Tom and to make a relationship between that table to your Manager dimension. thats way you will have to make only 1 RLS rule. you can read more about it here: 

http://radacad.com/dynamic-row-level-security-with-profiles-and-users-in-power-bi

[Randalnik]

Hello Drors,

thanks so much for your help. I was looking for something like this article.

I have tried the 1. solution and nothing happened.

I have tried the 2. solution. I Prepared the model, data, formulated DAX  just like in an article but im not able to see anything.

I'm logged in in my acc in Power BI Desktop and this email is contained in the table of users.

Right below is the model (Sorry, I dont have any time to translate it to english (its in czech)).

Zam_Detail = User

Zam_Pob_Security = User_Profile

Vydejni_Misto = Profile

Theres DAX saved in Vydejni_Misto in Manage Roles:

[Pobocka_Id] IN SELECTCOLUMNS (
FILTER (
'Zam_Pob_Security',
RELATED(Zam_Detail[Email])
=USERPRINCIPALNAME()),
"Vydejni_Misto",[Pobocka_Id])

I really know its hard to understand without seeing all the details or daa, but do you have some idea, how to fix it?

Thanks, R.

[Drors]

Hi,

Hi, It little bit hard to me to know excatly where is the problem but I can suggest some things,

in the first way: try to login to power bi cloud with diffrent user and check if its work (after publishing the new model and add the second user to the security in the cloud).

for the second way : try to write the DAX RLS in a measure, then put it in a card visual and check what is the output. if you dont get the desired output try change it a little bit untill it will be ok.

for both ways : you should check is that you dont have duplicates in your emails in the User table, press on the remove duplicates and then add an Index as a ID, make a relationship with the ID between the User and the User profile tables.

I used that article several times and its work fine to me.. so Im sure you will handle it also. 

Regards,

Dror

[Greg_Deckler]

So, RLS is set in both the Desktop and the Service. For each set of branches, you will need to first create a role, so 50 roles. So, your RLS rule will look like something along the lines of:

Table[Branch] = 12

This would be the rule for Branch12 for example.

Then you publish your file to the Service. In the Service you can then add members to each of the roles. So, you would add Tom to the Branch12 role, for example.