Forum Discussion

barragan82's avatar
barragan82
Helper II
11 months ago
Solved

Populate Data Into a Text Box

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!

 

Tips and Tricks
  • MasonMA's avatar
    MasonMA
    11 months ago

    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's avatar
    MasonMA
    11 months ago

    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 )
        & "."
)

     

6 Replies

  • MasonMA's avatar
    MasonMA
    Super User
    11 months ago

    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)
        ) & "."
)