topic Re: Need to extract values from string based on my list in DAX Commands and Tips

Need to extract values from string based on my list

Anonymous — Mon, 06 Feb 2023 20:33:14 GMT

Hi brainy guys!

I need to extract values form the string based on the values I have.

For example:

Values I need to find in the COLUMN - Names: ab,as,ad,af,ag,ah,aj

Table:

Num	Names	Expected result
2	blalsdl (fg/bv/df/as/gd/ad)	as,ad
3	testmn (gh/dg/ad/sf)	ad
4	tessdtmn (aj/dg/ad/sf)	aj,ad
5	tessdtmn (tr/sf)
6	fasdsadwq (gh/ad/sf,ah)	ad,ah

Re: Need to extract values from string based on my list

Greg_Deckler — Mon, 06 Feb 2023 21:47:48 GMT

Anonymous Here is one way, PBIX is attached below signature:

Column = VAR __ValuesToFind = { "ab", "as", "ad", "af", "ag", "ah", "aj" } VAR __Begin = FIND("(", [Names], ,0) + 1 VAR __End = LEN([Names]) VAR __Path = SUBSTITUTE(MID([Names],__Begin, __End - __Begin),"/","|") VAR __Table = ADDCOLUMNS( GENERATESERIES(1,PATHLENGTH(__Path),1), "__Value",PATHITEM(__Path,[Value]) ) VAR __Result = CONCATENATEX(INTERSECT(SELECTCOLUMNS(__Table,"Value",[__Value]), __ValuesToFind),[Value],",") RETURN __Result

Re: Need to extract values from string based on my list

Julia_1 — Wed, 08 Feb 2023 09:35:35 GMT

Hi Greg_Deckler , thank you, I was just looking for a similar solution, but this didn't work for me since I use Direct Query model. Please, any ideas, how to adjust it?

Re: Need to extract values from string based on my list

Greg_Deckler — Wed, 08 Feb 2023 12:46:43 GMT

Julia_1 Here it is as a measure:

Measure = VAR __Name = MAX('Table'[Names]) VAR __ValuesToFind = { "ab", "as", "ad", "af", "ag", "ah", "aj" } VAR __Begin = FIND("(", __Name, ,0) + 1 VAR __End = LEN(__Name) VAR __Path = SUBSTITUTE(MID(__Name,__Begin, __End - __Begin),"/","|") VAR __Table = ADDCOLUMNS( GENERATESERIES(1,PATHLENGTH(__Path),1), "__Value",PATHITEM(__Path,[Value]) ) VAR __Result = CONCATENATEX(INTERSECT(SELECTCOLUMNS(__Table,"Value",[__Value]), __ValuesToFind),[Value],",") RETURN __Result

Re: Need to extract values from string based on my list

Julia_1 — Thu, 09 Feb 2023 14:42:56 GMT

Hi Greg. Thank you for a quick response! I had to switch report to Import mode and calculated column worked for me just as expected. But the Measure in DirectQuery returns BLANK. I am trying to make ammendments to it