How to check if a string from a column in table A contains in table B and perform an action?

[Anonymous]

Hi Everyone,

I have a question on DAX formula which allows me to iterate over a column in one table and perform a concatnate action on another column if the value exsists in that column.

Example
I Have tableA as shown below

PathUsername

A001@test.net|B001@test.net|C001@test.net

A001@test.net|B001@test.net|C001@test.net|D001@test.net

(Image view so the columns are shown properly)

I Also have tableB as shown below

SearchString	AddString
D001@test.net	E001@test.net
C001@test.net	F001@test.net

(Image view so the columns are shown properly) 

What I'm trying to do is check if the each values in "SearchString" of tableB is in every row of "PathUsername" of tableA and if yes then concatnate the value of "AddString" corresponding to that "SearchString" from tableB to "PathUsername" of tableA as below

PathUsername	ProxyUsername
A001@test.net|B001@test.net|C001@test.net	A001@test.net|B001@test.net|C001@test.net|F001@test.net
A001@test.net|B001@test.net|C001@test.net|D001@test.net	A001@test.net|B001@test.net|C001@test.net|D001@test.net|E001@test.net|F001@test.net

(Image view below so the columns are shown properly)

If you notice, if there are multiple values it also needs to be concatnated(row two of above table includes the add string of C001 and also D001)

Kindly advise how I can acheive this in DAX thank you.

[amitchandak · Accepted solution]

@Anonymous , Try like

concatenatex(filter(tableB, containsstring(tableA[PathUsername], tableB[SearchString])),tableB[AddString],"|")

[Anonymous]

@amitchandak @Anonymous Thank you very much both the solution works with a little bit tweeking. Really appriciate your help.

[Anonymous · Accepted solution]

You may use this approach.

ProxyUserName =
tableA[PathUsername]
    & CONCATENATEX (
        tableB,
        IF (
            NOT (
                ISBLANK ( SEARCH ( tableB[SearchString], tableA[PathUsername], 1, BLANK () ) )
            ),
            tableB[AddString],
            BLANK ()
        ),
        "|"
    )

You will have to slightly modify the code to ensure that blanks and delimiters are added correctly.

[amitchandak · Accepted solution]

@Anonymous , Try like

concatenatex(filter(tableB, containsstring(tableA[PathUsername], tableB[SearchString])),tableB[AddString],"|")

[Anonymous]

@amitchandak 

I didn't knew that DAX has a function called CONTAINSSTRING. I just checked and found that there are few CONTAINS functions like CONTAINS, CONTAINSROW, CONTAINSTRING, CONTAINSTRINGEXACT, PATHCONTAINS. 
Are these new functions added recently?