Extracting all related materials from description strings

Hi  saturated_snail ,

Here are the steps you can follow：

1. Create calculated column.

Step1 =
var _search1=
    SEARCH("(",'Material Master'[Description],1,BLANK())
var _search2=
    SEARCH("[",'Material Master'[Description],1,BLANK())
    var _if1=
IF(
    _search1=BLANK(),BLANK(),
MID('Material Master'[Description],_search1+1,7))
var _if2=
IF(
    _search2=BLANK(),BLANK(),
MID('Material Master'[Description],_search2+1,7))
var _switch=
SWITCH(
    TRUE(),
    _if1= BLANK() ,_if2,
    _if2=BLANK(), _if1,
CONCATENATE(_if2, CONCATENATE("、", _if1))
)
return
_switch

Rank123 =
  VAR __Item =[Description]
  VAR __Text = CONCATENATEX('Material Master',[Description],"|")
  VAR __Count = PATHLENGTH(__Text)
  VAR __Table =
    ADDCOLUMNS(
      GENERATESERIES(1,__Count,1),
      "__Item",PATHITEM(__Text,[Value])
    )
  VAR __TableFinal =
    SUMMARIZE(__Table,[__Item],"Index",MINX(FILTER(__Table,[__Item]=EARLIER([__Item])),[Value]))
RETURN
  MINX(FILTER(__TableFinal,[__Item] = __Item),[Index])

group =
LEFT(
    'Material Master'[Material],2)

Step2.1 =
CONCATENATEX(
    FILTER(ALL('Material Master'),'Material Master'[Rank123]<=EARLIER('Material Master'[Rank123])+1&&'Material Master'[group]=EARLIER('Material Master'[group])),
    [Material],
 CONCATENATE("、", " "))

Step2.2 =
CALCULATE(MAX('Material Master'[Step2.1]),FILTER(ALL('Material Master'),
'Material Master'[group]=EARLIER('Material Master'[group])&&'Material Master'[Rank123]=MAXX(FILTER(ALL('Material Master'),'Material Master'[group]=EARLIER('Material Master'[group])),[Rank123])))

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly