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 instant, Product "A" is created by assembling "A1", "A2" and "A3" components which have "green", "green" and "blue" labels. Therefore Component table has the following structure:

 

PRODUCT_id  COMPONENT_id  LABEL
AA1green
AA2green
AA3blue
BB1green
BB2red

 

The result for A product in Product table is "green-green-blue" using the following formula:

 

CONCATENATEX(FILTER(ALL(Component); 'Product'[id] = Component[PRODUCT_id]); Component[label]; "-")

 

My goal is to obtain "green-blue" for Product A in Product table.

 

Does anyone how I can avoid duplicated words in a single labels?

 

Thanks in advance.

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]
)

     

     

     

6 Replies

  • 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

    Hi amitchandak  Thanks for your answer.

     

    However your measure formula does not run due to the different component labels for a single Product:

     

     

    Would it be possible to create a columns instead of a measure?

     

    Thanks for your help.

     

    Regards.

    • amitchandak's avatar
      amitchandak
      Super User
      4 years ago

      aramirez2 , do you have two tables? If yes, share sample data for both and sample output in table format