Hi, Is there a way to filter data view by de...

Dan_Velus
Participant IV

Hi,

Is there a way to filter data view by departments, while manager have access to all data?

Eg. Admin - 3 users / Payroll - 4 users / Sales - 5 users.

Each department is only allowed to see their own teams data, however, Manager is allowed to see all the teams data?

Different slices with filters for different departments - is this the only way? OR could this achieved through a security filter?

Please suggest.

0 5 4,061
5 REPLIES 5

@Daniel_Veluswamy You need a way to identify the user, to associate the user with a department, and to associate a data row with a department. You also need a way to identify โ€œManagerโ€.

To identify the user, the common method is to use the useremail() function.

To associate users with departments, you could create a new (or use an existing) table that does this. For instance, you could create a Staff table with the columns [Email] and [Department].

To identify โ€œManagerโ€, you could add a Y/N-type column to the Staff table called [Manager?].

In your data table (letโ€™s call it Data in this example), you need some way to indicate which rows belong to which departments. A simple way would be to have a column that names the department. You could call this column [Department].

In your slice or security filter row selector, you would use an expression like the following to include all rows if the current user โ€œis Managerโ€ OR only rows belonging to the current userโ€™s department:

or(lookup(useremail(), Staff, Email, Manager?), in([Department], select(Staff[Department], in(useremail(), list([Email]))))))

Either slices or security filter would work. A security filter has the advantage of limiting the amount of data sent to the app (reduces data usage and improves security).

Dan_Velus
Participant IV

+Steve Coile Thanks Steve. I understand. I did find this expression that filters by department <<LOOKUP(USEREMAIL(), Employees, Email, Department) = โ€œAdminโ€>> This filter allows the data to be viewed by all users under Admin department.

But how can we apply this for multiple departments with each department having multiple users. The data for each department must only be viewed by the members of that department. So Admin data is shared between the 3 users but not to Payroll and Admin departments - While Manager has access to all data.

small comment โ€” definitely use security filters for this and not slices.

Slices are not a security mechanism โ€” they are just a convenient mechnaism for filtering data

Dan_Velus
Participant IV

@praveen Thanks Praveen for your suggestion.

+Steve Coile Hi Steve, I canโ€™t thank you enough for this assistance. Much Appreciate it. The code worked perfectly. To help others who may be looking to do similar functions, I am detailing below what I did to make department security work.

Tables Created:

  1. Staff (Table Name) |

Columns - Email, Manager? (Yes/No) 2. Departments (Table Name) | Columns - Email, Department

PLUS

Included the column names - [Department] & [Owner] (With initial value USEREMAIL() - Email of the user who creates the line of data) in the data table. The column [Department] is referenced to table - Departments[Department] (Department column of table Departments).

This enables the data column to fetch the value of department automatically using the email of the user.

Security Filter on the Data Table:

OR(LOOKUP(USEREMAIL(), Staff, Email, Manager?), IN([Department], SELECT(Departments[Department], IN(USEREMAIL(), LIST([Email])))))

This perfectly did the job.

Now, I have Managers with overall visibility of all departments and Departments are separated to access their own data.

Thanks heaps Guys.

Top Labels in this Space