Forum Discussion

pdbenbow's avatar
pdbenbow
Icon for Resolver II rankResolver II
7 years ago
Solved

Concatenating data within RELATEDTABLE output

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: [email protected]

mailto: [email protected]

mailto: [email protected]

 

What I need to produce is this:

mailto: [email protected]; [email protected]; [email protected]

 

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: [email protected]; [email protected]; [email protected]; [email protected]; [email protected]; [email protected]

  • pdbenbow's avatar
    pdbenbow
    7 years ago

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

9 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago
    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's avatar
      pdbenbow
      Icon for Resolver II rankResolver II
      7 years ago

      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's avatar
        Anonymous
        Not applicable
        7 years ago
        [Email All] =
var __concatenatedEmails =
	CONCATENATEX(
		DISTINCT( ENVH[Email] ),
		ENVH[Email],
		"; "
	)
var __mailtoString =
	"mailto:" & __concatenatedEmails
return
	__mailtoString
  • pdbenbow's avatar
    pdbenbow
    Icon for Resolver II rankResolver II
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago
      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
  • crjackson's avatar
    crjackson
    Icon for Helper I rankHelper I
    3 years ago

    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's avatar
      pdbenbow
      Icon for Resolver II rankResolver II
      3 years ago

      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's avatar
        crjackson
        Icon for Helper I rankHelper I
        3 years ago

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