Forum Discussion

akj2784's avatar
akj2784
Icon for Post Partisan rankPost Partisan
8 years ago

Data level security on Power BI

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 ...
Need Help
Tadder's avatar
Tadder
Icon for Helper II rankHelper II
8 years ago

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's avatar
akj2784
Icon for Post Partisan rankPost Partisan
8 years ago

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()
)