Concatenating data within RELATEDTABLE output

[pdbenbow]

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

[crjackson]

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.

[pdbenbow]

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.

[crjackson]

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

[pdbenbow · Accepted solution]

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],"; ")

[Anonymous]

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

[Anonymous]

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.

[pdbenbow]

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.

[Anonymous]

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

[pdbenbow]

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.