Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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 |
A | A1 | green |
A | A2 | green |
A | A3 | blue |
B | B1 | green |
B | B2 | red |
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.
Solved! Go to Solution.
@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]
)
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.
@aramirez2 , do you have two tables? If yes, share sample data for both and sample output in table format
@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
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:
ID | Label |
A | green-blue |
B | green-red |
Thanks for your time
@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]
)
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).