Forum Discussion

barragan82's avatar
barragan82
Helper II
10 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?  I would like each result to be separated by a comma. I would like to include the word “and” before the last resul...
Tips and Tricks
  • MasonMA's avatar
    MasonMA
    10 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
    10 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 )
        & "."
)

     

MasonMA's avatar
MasonMA
Super User
10 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)
        ) & "."
)

 

 

  • barragan82's avatar
    barragan82
    Helper II
    10 months ago

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

    • MasonMA's avatar
      MasonMA
      Super User
      10 months ago

      Happy to see this works for you:) 

      • barragan82's avatar
        barragan82
        Helper II
        10 months ago

        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!