Trying to return comma separated values from filtered related records in a column
I am trying to find the right formula in DAX for this and getting nowhere.
I have two tables:
The first table is Projects, this has a ProjectID column, and many others.
The second table is Contacts related to projects, which also has a ProjectID column, which is used to relate contacts to projects many to one. There is also a Role column and a Name column on the Contact table.
I need to create a column on the Projects table which shows, in a comma separated list, the Names of all the contacts where 1) ProjectID on the Contact = ProjectID on the Project, and 2) the Role value for that row of the Contact table contains a certain string.
I have used CALCULATE successfully to do the same kind of thing, summing values on a related table with multiple filter criteria applied to which rows are included, but I'm failing on getting text values concatenated into a comma separated string in the same way.