Power BI: combine words based on criteria

This article will describe how to combine texts based on values, something like that:

power bi

I will start with the basic, for 1 table:


And if I want to separate each word with a coma:


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

I have 2 tables:

Table1 Table2
power bi power bi

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

power bi


  • Change “table” and “argument” by yours
  • Alternative: CONCATENATEX(FILTER(ALL('Table1'),'Table1'[argument1]='Table2'[argument1]),'Table1'[argument3]," ")
  • 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:

power bi

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

power bi

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

power bi

This is the result:

power bi

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]," ")
    power bi
  • 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]," ")
    power bi

Interesting Management