Concatenating text based on criteria

[gambleave]

I would be grateful for advice. As depicted, I have a data table containing service interaction records that is linked to an index table containing employee names and their practice.

Currently I use a simple measure to concatenate the names of clients (“Contact Name”) who have received high touch (“HT”) service:

HT Recipients
=calculate(CONCATENATEX(values(ServiceDetails[Contact Name]), ServiceDetails[Contact Name],”, “), ServiceDetail[Service Type]=”HT”)

What I would like to do is to create a matrix/pivot table where I can select a particular employee, and for each Client (in rows), display the concatenated names of contacts for which other employees under the same Practice have service interactions – but the particular employee does not.

Even better (not sure if this part is actually possible), it would be great if the names appeared in descending order of how many interaction records exist for the contact. I have an existing measure for this count:

Distinct HT =calculate(DISTINCTCOUNT(ServiceDetails[Activity ID]), ServiceDetails[Service Type]=”HT”)

I would greatly appreciate some advice on how to approach this problem.

[bolfri]

Can you share a sample data and expected results eg in excel? It will be easier.

[gambleave]

Afraid the data is in a corporate network and can't be shared. I made the sample screens through VDI on my Mac as I don't have PowerBI at home.