Matrix & Concatenations with universal connections

[Wlknsn]

Hi all,

Special one this time. I have the following model:

The goal is to be able to select any 2 dimensions (accounts, contacts, contracts & users), and show a concatenation of the roles for that connection in a matrix. As you can see from the model, the connections can be linked to any type of record but they always have a role.

Example Connection: “Frank Anderson” (Contact) | “Microsoft” (Account) | “Employee” (Role).

Any ideas?

Regards,

Christopher

[EnterpriseDNA]

Hi @Wlknsn, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

• Use the forum search to discover if your query has been asked before by another member.

• When posting a topic with formula make sure that it is correctly formatted to preformatted text </>.

  

• Use the proper category that best describes your topic

• Provide as much context to a question as possible.

• Include the masked demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline How To Use The Enterprise DNA Support Forum. Not adhering to it may sometimes cause delay in getting an answer.

Please also check the How To Mask Sensitive Data thread for some tips on how to mask your pbix file.

[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

[Wlknsn]

Hi Brian,

A sample dataset coming your way later today!

Many thanks in advance!

Regards,

Christopher

[BrianJ]

@Wlknsn,

Thanks – look forward to seeing it. So I take it the matrix display of the concatenated results was not the issue…

• Brian

[EnterpriseDNA]

Hi @Wlknsn, did the response provided by @BrianJ help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

[EnterpriseDNA]

A response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Thanks!