Forum Discussion

aramirez2's avatar
aramirez2
Helper I
4 years ago
Solved

Clean duplicate words in a string using CONCATENATEX funciton

Hi.   I have a issue trying to concatenate strings.   My model contains 2 tables: Product and Component. Each product is built up by 2 or more components. Each component contains a label.   For...
Need Help
  • smpa01's avatar
    smpa01
    4 years ago

    aramirez2  you can use a measure like this

     

    Measure =
CONCATENATEX (
    VALUES ( Component[LABEL] ),
    Component[LABEL],
    ",",
    Component[LABEL], ASC
)

     

     

     

    If you need a calculated column

    Column =
CONCATENATEX (
    SUMMARIZE ( RELATEDTABLE ( Component ), Component[LABEL] ),
    CALCULATE ( MAX ( Component[LABEL] ) ),
    ",",
    Component[LABEL]
)

     

     

     

amitchandak's avatar
amitchandak
Super User
4 years ago

aramirez2 , Try a measure like

Measure = CONCATENATEX(summarize(FILTER('Product', 'Product'[PRODUCT_id] = max('Product'[PRODUCT_id])),'Product'[  LABEL]), 'Product'[  LABEL], "-")

 

Assumed product as table na,e

aramirez2's avatar
aramirez2
Helper I
4 years ago

Thanks amitchandak 

 

Your formula is running now but the result is not as expected. There are still duplicated words as at the beginning: "green-green-blue".

 

My model contains two tables:

 

- Table Product with only ID column. Furthermore I want a new column "Label" which should concatenate all their components labels.

- Table Component with 3 columns; PRODUCT_id, COMPONENT_id and LABEL (as my previous table example)

 

So expected Product table result is:

 

IDLabel
Agreen-blue
Bgreen-red

 

Thanks for your time

  • smpa01's avatar
    smpa01
    Community Champion
    4 years ago

    aramirez2  you can use a measure like this

     

    Measure =
CONCATENATEX (
    VALUES ( Component[LABEL] ),
    Component[LABEL],
    ",",
    Component[LABEL], ASC
)

     

     

     

    If you need a calculated column

    Column =
CONCATENATEX (
    SUMMARIZE ( RELATEDTABLE ( Component ), Component[LABEL] ),
    CALCULATE ( MAX ( Component[LABEL] ) ),
    ",",
    Component[LABEL]
)

     

     

     

    • aramirez2's avatar
      aramirez2
      Helper I
      4 years ago

      Thanks a million! smpa01 

       

      I avoided duplicates with your solution. I only had to connect both tables to make your column and measure work (they were not connected previously).