topic Concatenating data within RELATEDTABLE output in DAX Commands and Tips

Concatenating data within RELATEDTABLE output

pdbenbow — Thu, 15 Aug 2019 16:36:19 GMT

My report has a list of email addresses, and the request from the users is to have a "mailto" hyperlink they can click to send an email to all addresses. The list needs to be dynamic based on slicer selections. For example, if there are six addresses in the table but the user selects a slicer to narrow the list to three addresses, the hyperlink should only display those three addresses.

What I have so far:

Email All = "mailto:" & CONCATENATEX( RELATEDTABLE(ENVH), ENVH[Email], "; ")

Produces this:

mailto: userBob@myemail.com

mailto: userJohanna@myemail.com

mailto: userTom@myemail.com

What I need to produce is this:

mailto: userBob@myemail.com; userJohanna@myemail.com; userTom@myemail.com

I originally tried this:

Email All = "mailto:" & CONCATENATEX( ENVH, ENVH[Email], "; ")

But that gives me all six addresses, and ignores my slicer selections (addresses in red should NOT be included)...

mailto: userBob@myemail.com; userJohanna@myemail.com; userTom@myemail.com; userBeth@myemail.com; userPaul@myemail.com; userGeorge@myemail.com

Re: Concatenating data within RELATEDTABLE output

Anonymous — Thu, 15 Aug 2019 17:49:44 GMT

Can you show the model, please? RELATEDTABLE goes from the one side of the relation to the many side. I'd like to know what you store in your tables and how they're linked and by which fields you slice. Without this knowledge I don't think anyone could give you a correct answer...

Best
D.

Re: Concatenating data within RELATEDTABLE output

pdbenbow — Thu, 15 Aug 2019 17:54:03 GMT

The data model is a single table with no relationships. That's why I don't think RELATEDTABLE is what I should be using. There must be another way to concatenate the values after they've been sliced.

Re: Concatenating data within RELATEDTABLE output

Anonymous — Thu, 15 Aug 2019 17:57:25 GMT

[Email All] =
var __concatenatedEmails =
	CONCATENATEX(
		DISTINCT( ENVH[Email] ),
		ENVH[Email],
		"; "
	)
var __mailtoString =
	"mailto:" & __concatenatedEmails
return
	__mailtoString

Re: Concatenating data within RELATEDTABLE output

pdbenbow — Thu, 15 Aug 2019 18:08:13 GMT

Nope, doesn't work. That gives me the exact same result as my original formula:

Original formula:

Email All = "mailto:" & CONCATENATEX( ENVH, ENVH[Email], "; ")

Here is the output when not sliced:

Here is the output when sliced:

The slicer is having no effect. When I toggle that slicer, there should only be three email addresses listed.

Re: Concatenating data within RELATEDTABLE output

pdbenbow — Wed, 21 Aug 2019 12:24:05 GMT

I was able to get what I needed by converting the calculated column into a measure.

DAX:

Email All = "mailto: " & CONCATENATEX(VALUES(ENVH[Email]),ENVH[Email],"; ")

Re: Concatenating data within RELATEDTABLE output

Anonymous — Wed, 21 Aug 2019 18:05:07 GMT

Well, my formula was A MEASURE. When I create a calculated column I state this fact explicitly.

Slicers NEVER affect calculated columns the way you thought. They can only filter the columns for certain values that have been precalculated in there, nothing else.

Best
Darek

Re: Concatenating data within RELATEDTABLE output

crjackson — Fri, 28 Apr 2023 22:18:56 GMT

can you make an actual button with this measure? i got it to work but it will only work in a table. I set the button to weburl and add the measure but nothing happens.

Re: Concatenating data within RELATEDTABLE output

pdbenbow — Mon, 01 May 2023 11:56:51 GMT

We got it to work with a button. Not sure if this applies to your situation, but "mailto" has a limit of 2,000 characters. We found the button would not function if the concatenated list was over 2,000 characters long. We used slicers so our users could break the list down into smaller, more manageable groups. This allows them to get the "mailto" under 2,000 characters, which allows the button to function.

Re: Concatenating data within RELATEDTABLE output

crjackson — Sat, 06 May 2023 00:57:33 GMT

Hmmm. I have a slicer and it still doesn't work for me even with just two email addresses