Data level security on Power BI

[akj2784]

Hi All,

I have data as below .

When Akash logs in, the dashboard should default to Pm Code = 1234, when Ajay logs in, it should default to Pm Code =3234

And it should not prompt user to select any value. Automatically it should apply data level secutiry based on whoever logs in. How do we achieve such feature in power BI.

Basically how do we capture who has logged in and then write a query to find what is the PM Code for his login should help.

[v-huizhn-msft]

Hi @akj2784,

There is a blog that describes how to use Data level security in detailed steps, please review it.

https://datachant.com/2017/05/24/row-level-security-power-bi/

Best Regards,
Angelia

[akj2784]

Thanks. How do I know the user name/userid format accepted by Power BI. Name can be duplicate so I would like to drive it from User Id which is unique. 

Lets say I have the data as show below in the roles table.

UserId                 PMCode

AKJAIN                1234

VJAIN                   4321

AJAIN                   3234

So I want to check the USERId of the person who logged in, then I would like to fetch its corresponding PM Code from this roles table. And then I would like to equate the transaction table with PM Code = the value fetched from above. So that the logged in user can see only relevant data.

So for above scenario, I would like to understand How I should create the Roles from Manager Roles feature in Power BI Desktop.

[KHorseman]

@akj2784as I have already said, the username in Power BI is the Microsoft username, which generally takes the form of the user's email address in Office 365 but may be slightly different depending on your organization's specific setup. You need to add a column to this table with those usernames and use those.

[akj2784]

ok. Lets assume I have this column in the table. 

So for each user I have PM Code tagged to it in the table. 

And I want to equate this PM Code fetched for each user to the Actual project table which has PM Code in it so that it just shows his Projects.

Would you be able to guide me with the steps .?

Thanks for your help.

Regards,

Akash

[akj2784]

I actually tried creating roles with table[username]= USERNAME( )

But not sure how to assign the PM Code for the logged in user to the actualy Project table which has PM Info.

[KHorseman]

You don't do anything with the PM code. Presumably you have a relationship between this user table and your regular data, and you normally use it to filter and categorize that data. I don't know your specific case but for example imagine that your user table is a table of sales reps where each username is accompanied by a matching email address column, and linked to that table you have a table of sales data. You show sales results by sales rep in your report. If you create a "sales rep" security role with a filter on the user table of usertable[email address] = USERNAME(), then publish that report and add each of the sales rep users to that security role on the service, when they look at the report it will show only their individual sales results and nobody else's.

[akj2784]

ok. I see your point but that's not exactly what I want to implement. 

Thank you for your guidance.

In my actual project table I don't have user name/email. All I have is PM Code. I have to drive the dashboard based on that and apply the data level security so that each PM can see only his Projects in the dashboard.

[Tadder]

You need to add a column to your users table for their Office365 email account. Then do a lookup in the row level security based on that.  USERPRINCIPALNAME() returns the current users login (email address).  So the filter is on PM_CODE.  LOOKUPVALUE() is looking for the PM_CODE of the USERPRINCIPALNAME and uses the EMAIL_ADDRESS as a cross reference.  The result of this is filtering for PM_CODE = AKASH or whoever the logged in user is.

It would look like this:

[akj2784]

That did not work. Let me elaborate the scenario.

I have two tables Project and Security. Project is a dimension table which is joined to many fact tables to get the revenue, forecast etc.

And Security tables holds who can see what. So Akash can see only Projects for PmCode=1234, similarly Ajay can see only Projects where PMCode=3234

I tried to add the following on the PMCode of Security table, but it is not restricting the data correctly.

[PMCode] = LOOKUPVALUE(
[PMCode],
[UserName], USERPRINCIPALNAME()
)

[Tadder]

I'm noticed there are spaces in the Headers for the Security table, and no spaces in the LOOKUPVALUE function (PM CODE vs PMCODE).  Problably not the issue, but better safe than sorry, so check that out.

Add a new measure in PBI Desktop for LoggedInAs = USERPRINCIPALNAME().   Add a card to your report (temporarily) to show who is logged in.  Switch to View As a different user.  Does LoggedInAs show what you expect?  It should show AKASH's email address if done properly.

Actually - Now that I've read a little closer - you're using the wrong User field in the lookup.  Use UserID instead of UserName. You want to look up the EMAIL and return the PMCODE. Should not use USERNAME at all. 🙂 But showing who the logged in user is on the report is pretty cool too.  I use it.

[KHorseman]

Well that's not really how row level security works. You're going to have to come up with some way for the Power BI service to recognize the user's PM code by their Microsoft user account. The only way to do that is to use that user account as an identifier. The account is the email address.

[KHorseman]

Sure there is. You just need another column with the user's Microsoft username (usually their email address but it depends on your Office 365 setup). Then you enable row level security and set a filter there for UserTable[MSUsername] = USERNAME(). Publish the report, add all the users to this role on the service and you're all set.