Forum Discussion
Controlling Multiple Name Columns with One Master List Column
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.
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 TeamIf 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!
2 Replies
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 TeamIf 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!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.
