Populate Data Into a Text Box

[barragan82]

Hello,

Is there a way to create a measure that will populate data in a text box? 

1. I would like each result to be separated by a comma.
2. I would like to include the word “and” before the last result.

Below is an example of what I am trying to accomplish. I would like the data from the Assignment Type column to automatically populate after the word “are” and include the word "and" before "Short Written Response" (the last result). Thank you for any help!

[MasonMA · Accepted solution]

@barragan82 

Hi, try this sample code a DAX Measure. Replace Table and Column names with your actual ones, also replace the text part for Single item display and Multiple items display. 

Text = 
VAR ItemsList =
    DISTINCT('Table'[Country])
VAR CountItems =
    COUNTROWS(ItemsList)
VAR ConcatText =
    CONCATENATEX(
        ItemsList,
        'Table'[Country],
        ", ",
        'Table'[Country],
        ASC
    )
RETURN
IF(
    CountItems = 1,
    "Country in Column is: " & MAX('Table'[Country]) & ".",
    "Countries in Column are " &
        SUBSTITUTE(
            ConcatText,
            ", " & LASTNONBLANK('Table'[Country], 1),
            ", and " & LASTNONBLANK('Table'[Country], 1)
        ) & "."
)

[MasonMA · Accepted solution]

@barragan82 

Hi, try this sample code a DAX Measure. Replace Table and Column names with your actual ones, also replace the text part for Single item display and Multiple items display. 

Text = 
VAR ItemsList =
    DISTINCT('Table'[Country])
VAR CountItems =
    COUNTROWS(ItemsList)
VAR ConcatText =
    CONCATENATEX(
        ItemsList,
        'Table'[Country],
        ", ",
        'Table'[Country],
        ASC
    )
RETURN
IF(
    CountItems = 1,
    "Country in Column is: " & MAX('Table'[Country]) & ".",
    "Countries in Column are " &
        SUBSTITUTE(
            ConcatText,
            ", " & LASTNONBLANK('Table'[Country], 1),
            ", and " & LASTNONBLANK('Table'[Country], 1)
        ) & "."
)

[barragan82]

@MasonMA , you really are a rockstar! This is excactly what I needed. You rock! Many thanks. 

[MasonMA]

Happy to see this works for you:) 

[barragan82]

Is there a way to modify this measure so that it doesn't include blank entries? At the moment, I"m getting a space and a comma for blank entries (ex.  , India, UK, and USA). Thanks again for all your help!

[MasonMA · Accepted solution]

You can tweak some more based on this sample code

Text = 
VAR ItemsList =
    FILTER(
        DISTINCT( 'Table'[Country] ),
        LEN( TRIM( COALESCE( 'Table'[Country], "" ) ) ) > 0
    )
VAR CountItems = COUNTROWS( ItemsList )
VAR ConcatText =
    CONCATENATEX(
        ItemsList,
        TRIM( COALESCE( 'Table'[Country], "" ) ),
        " ",
        TRIM( COALESCE( 'Table'[Country], "" ) ),
        ASC
    )
VAR LastItem =
    MAXX( ItemsList, TRIM( COALESCE( 'Table'[Country], "" ) ) )
RETURN
SWITCH(
    TRUE(),
    CountItems = 0, BLANK(),
    CountItems = 1, "Country in Column is " & LastItem & ".",
    "Countries in Column are " &
        SUBSTITUTE( ConcatText, " " & LastItem, " and " & LastItem )
        & "."
)

[barragan82]

This is great! Huge help! Thanks again!