Forum Discussion
Default Security Roles
Hi there!
So I'm making a PowerBI app for tracking employee performance.
I two different reports, one "individual" one for all employees, and one for management. For the individual one, I want the user to strictly be able to see their own data and no on elses. What I'm doing for this is I grab the user's company email with USERPRINCIPALNAME() and then filter that out from our employee table. The management one is so that managers can see monitor everyone's performance including their own.
I'm trying to use the security roles, in which if you're this specific role then the table filters based on your email. However, I don't believe there is a way for me to actually make this the default role. This means that anyone who goes on, it won't actually filter by their email.
I'm looking for any solution for this; my thoughts are either finding out how to make this role the default role (the company is huge so assigning everyone individually would just take way too long and too much to maintain). Or, just finding another solution like somehow permanently filtering it by email UNLESS you have a higher security role, because there will be a much smaller easily maintained quantity of managers over individual users.
Thank you!
Hi, elijahc
First, create a security table that maps users to their roles and the data they are allowed to access. This table can be stored in your database. I create a sample table:
During the ETL (Extract, Transform, Load) process, filter the data based on the user’s role and email. This can be done using SQL queries or a data transformation tool.
Example SQL Query:
SELECT * FROM EmployeePerformance WHERE (UserEmail = '[email protected]' AND AccessibleData = 'Employee1Data') OR (Role = 'Manager' AND AccessibleData = 'AllData')Pass user-specific parameters to your Power BI queries to filter data dynamically. You can use Power Query to achieve this. Create a Parameter for User Email:
Name the parameter and set its value to UserEmail and USERPRINCIPALNAME()
In Power Query Editor, filter the data based on the parameter UserEmail
let Source = Sql.Database("ServerName", "DatabaseName"), EmployeePerformance = Source{[Schema="dbo", Item="EmployeePerformance"]}[Data], FilteredData = Table.SelectRows(EmployeePerformance, each ([UserEmail] = UserEmail or [Role] = "Manager")) in FilteredDataDeploy your Power BI report and test it with different user accounts to ensure that the data is filtered correctly based on the user’s role and email.
Or just use dynamic RLS.
Select Manage Roles
Then create role named employee, and add the following DAX expression.
Create another role, such as Manager, without adding any filters.
In the Power BI service, assign these roles to the appropriate users.
Publish your Power BI report to the Power BI service. In the Power BI service, select your dataset and tap Security. Add the appropriate user or group for each role.
How to Get Your Question Answered Quickly
Best Regards
Yongkang Hua
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
Hi elijahc
Usually IT team will have Azure AD groups which can be used for this purpose, You can enter the AD group[ which will contain all emp list] in the email address for RLS in power bi Service.
Let me know if this helps.
If this post helps, then please consider Accept it as the solution to help the others find it more quickly. Appreciate you kudos!!
Follow me on LinkedIn!!!
LinkedInI'm not understanding; how/where would I find the "all emp list" AD group to assign the role in PowerBI? I've searched various things in this field for assigning employees to the role:
All that shows up are email addresses, so I don't see how I could assign a group/list.
Hi, elijahc
First, create a security table that maps users to their roles and the data they are allowed to access. This table can be stored in your database. I create a sample table:
During the ETL (Extract, Transform, Load) process, filter the data based on the user’s role and email. This can be done using SQL queries or a data transformation tool.
Example SQL Query:
SELECT * FROM EmployeePerformance WHERE (UserEmail = '[email protected]' AND AccessibleData = 'Employee1Data') OR (Role = 'Manager' AND AccessibleData = 'AllData')Pass user-specific parameters to your Power BI queries to filter data dynamically. You can use Power Query to achieve this. Create a Parameter for User Email:
Name the parameter and set its value to UserEmail and USERPRINCIPALNAME()
In Power Query Editor, filter the data based on the parameter UserEmail
let Source = Sql.Database("ServerName", "DatabaseName"), EmployeePerformance = Source{[Schema="dbo", Item="EmployeePerformance"]}[Data], FilteredData = Table.SelectRows(EmployeePerformance, each ([UserEmail] = UserEmail or [Role] = "Manager")) in FilteredDataDeploy your Power BI report and test it with different user accounts to ensure that the data is filtered correctly based on the user’s role and email.
Or just use dynamic RLS.
Select Manage Roles
Then create role named employee, and add the following DAX expression.
Create another role, such as Manager, without adding any filters.
In the Power BI service, assign these roles to the appropriate users.
Publish your Power BI report to the Power BI service. In the Power BI service, select your dataset and tap Security. Add the appropriate user or group for each role.
How to Get Your Question Answered Quickly
Best Regards
Yongkang Hua
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
