Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Score big with last-minute savings on the final tickets to FabCon Vienna. Secure your discount

Reply
Anonymous
Not applicable

Need to extract values from string based on my list

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: 

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

 

4 REPLIES 4
Julia_1
Frequent Visitor

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?

@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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

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 

Greg_Deckler
Community Champion
Community Champion

@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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.