Re: Advanced Filter Company and Then Department

djbginns · 07-13-2023 07:31 AM

Hi Guys,

@dbaum , @Suvrutt_Gurjar , @WillowMobileSys , @Steve

I am having difficulty creating a filter slice for a task manager. I am trying to create a filter that matches the Company, then the Department, then the Employee. Employee = employee username that is logged in. All of these filters can be created in a dependency table. "Assignment Log"

The table I am trying to slice is a "master task manager"

In this table I have a column name [company] and a column name [department], these can be assigned if they meet the following dependent conditions in the Assignment Log.

1. Company --> Department -- > Employee(Name) --> Job

also in this "master task manager" there is an assignment column that looks up the employee name and converts it to an email: from the table " Employee Filter"

Select(Employee Filter[Employee Email],In([Employee Name],[_THISROW].[Employee Name]))

From this, I have been able to build a filter view for each user that is assigned a task.

In(Lookup(Useremail(),"Employee Filter","Employee Email","Employee Email"),[Task Assignment])

My next task that I am unable to determine is

If I have a department manager who is assigned to multiple companies and specific departments but is not assigned to the task, how can I create a visible table that he can see based on the conditions in a dependency table?

Employee Email --> Company --> Department

note that sometimes there is a manager who has access to two companies but different departments within those companies.

This Formula is currently working for an employee who is assigned to one company and multiple departments. However, as soon as I add another dependency to allow access to two companies, it is now showing other departments in the second company that they should not have visibility and don't meet the condition to see them.

AND(ISNOTBLANK([Company]),IN([Company],SELECT(User Assignment Slice[Department],AND(ISNOTBLANK([Employee]),([Employee] = USEREMAIL())))))

Please let me know if there is any clarification. thank !

WillowMobileSys

@djbginns wrote:

If I have a department manager who is assigned to multiple companies and specific departments but is not assigned to the task, how can I create a visible table that he can see based on the conditions in a dependency table?

Employee Email --> Company --> Department

note that sometimes there is a manager who has access to two companies but different departments within those companies.

Not sure I understand everything but I'll comment as much as I can.

First, since you have departments within the companies, Employees should never be assigned directly to a Company. Instead they are assigned to a Department and then the Company is derived from that Department.

Second, make sure Departments are unique (by ID) across all companies even if they have the same name. For example, maybe each company has a department with the name "Sales Department". Each of these Sales Departments should have their own ID in the Departments table so that when you look it up there is 1 and only 1 Company associated with it. The Department ID is what is used for the Employee assignment or assignments, if more than one.

@djbginns wrote:

This Formula is currently working for an employee who is assigned to one company and multiple departments. However, as soon as I add another dependency to allow access to two companies, it is now showing other departments in the second company

This part makes me think that you are sharing Department records across the companies. If you are then there is no way to tell which company department you really need and is likely why you are having the issue.