Controlling Multiple Name Columns with One Master List Column

[Greenleaf]

Hello, I have an issue I've been working on to no avail. I have two columns in my Main DAX query, one is MainQuery[ApproachedBy] and the other is MainQuery[SuitabilityBy]. ApproachedBy references the number of approaches an employee does, while SuitabilityBy references the number of deferrals. On a single case, the person who approaches and the person who determines suitabillity may be different people or the same person. For example:

CaseFileId	ApproachedBy	Consented	SuitabilityBy	Deferral
10001	James	1	Tammy	1
10002	Beth	0	Beth	1
10003	James	1	Beth	0

Separately, I have another query from SQL that pulls in the master list of every employee name named EmployeeList[FullName].

Currently, I have one table that shows approaches, and one table that shows suitability determinations.

ApproachedBy	Consents
James	9
Beth	8

SuitabillityBy	Deferrals
Tammy	7
Beth	15

Is there a way to use the master list from EmployeeList[FullName] as a slicer which will control both tables simultaneously? So for example, if I choose 'Beth' from EmployeeList[FullName] it will filter approachedBy to Beths count, and suitabilityby to show Beths count?

Thank you for any help you can offer.

[v-linhuizh-msft · Accepted solution]

Thank you for your reply , vanessafvg . Here I have a idea in mind, and I would like to share it for reference.

Hi @Greenleaf ,

Based on your description, l created simple data.

MainQuery

EmployeeList:

Please follow the steps below:

1.Create two measures:

Consents =CALCULATE ( COUNTROWS ( MainQuery ), MainQuery[Consented] = 1 )

Deferrals = CALCULATE( COUNTROWS(MainQuery), MainQuery[Deferral] = 1)

2.Create two new tables:

Table 1 = SUMMARIZE('MainQuery','MainQuery'[ApproachedBy],"Consents",'MainQuery'[Consents])

Table 2 = SUMMARIZE('MainQuery','MainQuery'[SuitabilityBy],"Deferrals",'MainQuery'[Deferrals])

3.Then create relationships between new tables and EmployeeList as follows:

4.Create two tables using the fields of new tables, and add a slicer to your report using EmployeeList[FullName], the final results are as follows:

Best Regards,
Zhu
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[vanessafvg]

create a new table which is a unique list of all the names in both tables.  and call it name (or something) make sure there are no duplicates.  then link both tables to the new table ie map a relationship from name to suitability by and name to approach by.  then use the new table as your slicer and it will filter both to that persons name if there is a valid record in both tables.