This article will describe how to combine texts based on values in a calculated column but if you are more interested to do that in Power Query Editor, read Power BI: combine multiple values.

I will start with the basic, for 1 table:

• Horizontal
  • 'table1'[argument1]&" "&'table1'[argument2]&" "&'table1'[argument3]
  • CONCATENATE('table1'[argument1]&" ",'table1'[argument2]&" "&'table1'[argument3])

• Vertical
  • CONCATENATEX('table1','table1'[argument1]," ")
  • CONCATENATEX('table1','table1'[argument1]&" "&'table1'[argument2]," ")

NOTE: for both, to separate with something else, for instance with a comma, just change " " by ","

If I have an index column for instance, I can do it for specific rows:

CONCATENATEX(FILTER('table1','table1'[Index]>=10 && 'table1'[Index]<=20),'table1'[argument1]," ")

If I have duplicate ID (for instance, for the incident column), I will use this formula:

CONCATENATEX(FILTER('table1','table1'[Incident]=EARLIER('table1'[Incident])),'table1'[argument1]," ")

If the column, that I want to combine, has empty cells, I will use this formula:

CONCATENATEX(FILTER('table1','table1'[Incident]=EARLIER('table1'[Incident]) && NOT(ISBLANK('table1'[argument1]))),'table1'[argument1]," ")

And if I want to sort it:

CONCATENATEX(FILTER('table1','table1'[Incident]=EARLIER('table1'[Incident])),'table1'[argument1]," ",'table1'[Index],ASC)

NOTE: for descending order, change ASC by DESC

I have 2 tables:

Table1	Table2

The goal is to combine all incidents occurred for each group in the table2. For that, I will add a new column and I will put this formula:

CONCATENATEX(FILTER('table1',CONTAINSSTRING('table1'[argument1],'table2'[argument1])),'table1'[argument3]," ")

NOTE:

• Change “table” and “argument” by yours
• Each word is separated by a space, if I want something else, I will change the end of the formula " " by "," (coma), ", " (coma space), etc.

This formula will search and find all incidents based on 1 keyword which is the group type and this is the result:

If there is no need to search for a word, it is best to use this formula:

CONCATENATEX(FILTER('table1','table1'[argument1]='table2'[argument1]),'table1'[argument3]," ")

In the other hand, if I want all incidents for a single specific group, I will replace 'table2'[argument1] by the value, for instance:

CONCATENATEX(FILTER('table1',CONTAINSSTRING('table1'[argument1],"keyword")),'table1'[argument3]," ")

Now I want the same thing but with 2 criteria, in this case, I will add another CONTAINSSTRING:

CONCATENATEX(FILTER('table1',CONTAINSSTRING('table1'[argument1],'table2'[argument1]) && CONTAINSSTRING('table1'[argument2],'table2'[argument2])),'table1'[argument3]," ")

This is the result:

If I need more criteria, I just need to add another CONTAINSSTRING. To combine “and” (&&) and “or” (||) conditions:

CONCATENATEX(FILTER('table1',CONTAINSSTRING('table1'[argument1],'table2'[argument1]) && (CONTAINSSTRING('table1'[argument2],"value1")||CONTAINSSTRING('table1'[argument2],"value2"))),'table1'[argument3]," ")

About to get the unique value, I will include SUMMARIZE in the formula:

• For 1 keyword: CONCATENATEX(FILTER(SUMMARIZE('table1','table1'[argument3],'table1'[argument1]),CONTAINSSTRING(
  'table1'[argument1],'table2'[argument1])),'table1'[argument3]," ")
  
• For 2 and more: CONCATENATEX(FILTER(SUMMARIZE('table1','table1'[argument3],'table1'[argument1],'table1'[argument2]),
  CONTAINSSTRING('table1'[argument1],'table2'[argument1]) && CONTAINSSTRING('table1'[argument2],'table2'[argument2])),'table1'[argument3]," ")
  

NOTE: to combine values without any conditions, read Power BI: match values between 2 columns then concatenate