Matrix & Concatenations with universal connections

[BrianJ]

@Wlknsn,

Looks like an interesting problem. One of the things that would be helpful is better understanding where you’re running into problems with your solution. Is it a) calculation of the roles, given the dimension selections or b) displaying the selected concatenations in the matrix?

If the latter, I think the CONCATENATEX/UNICHAR( 10 ) combo may solve your problem. This is one of my favorite tricks, since while it technically returns a scalar, the result looks like it’s returning a table. Here are some prior solutions I’ve posted using this approach:

Displaying Names in a Table

@KieftyKids, I worked out a similar solution with another member a few months ago. It’s definitely trickier than it seems at first glance, because you have to apply all the context within the measure, and then get it to generate the vertical list. The trick is to use CONCATENATEX() with UNICHAR(10) as the delimiter (UNICHAR(10) = hard return). Clubs Visited List = CALCULATE( CONCATENATEX( VALUES('Sales Data'[Club Code]), 'Sales Data'[Club Code], UNICHAR(10), …

Carriage returns from Sharepoint List

@radley, Okay, thanks very much for the clear explanation of the requirement with example. Here’s the outcome, with your desired format on the left and my solution on the right: [image] Here’s what I did: Split the SharePoint list by lowercase to uppercase Unpivoted the resulting names column Added an index column (to maintain the name order in the following measure) Use the CONCATENATEX, UNICHAR(10) trick discussed above in the following measure, to concatenate all the names on…

If the former, I be glad to work on this, but it’s more than I can do in my head as a thought experiment, and will need an anonymized PBIX file and a clear mockup of the end result you are trying to achieve.

I hope this is helpful.

– Brian