Return "Yes" if field exists in another field in the table

[nanuli]

Hello! I have run a search which results in displaying a table. In this table, I would like to check if a combination of values between two fields exists, and, if so, return "Yes." I have done this in PowerBI using the following command, but I am unsure how to do it in SPL.

VAR _SEL =

SELECTCOLUMNS('table1', "code1", [code1])

RETURN IF ('table1'[code2] IN _SEL, "Yes", "No")

An example initial table is below:

id, code1, code2

1, ab, cd

2, cd, de

3, ab, hi

4, cd, ab 

5, jk, cd

6, hi, jk

7, jk, hi

The result I am looking for is that it will find that the combination of ab+cd and hi+jk exists in both directions (code1, code2 and code2, code1). 

id, code1, code2, result

1, ab, cd, yes

2, cd, de, no

3, ab, hi, no

4, cd, ab, yes 

5, jk, cd, no

6, hi, jk, yes

7, jk, hi, yes

Thank you for your help!

[ITWhisperer]

| eval result=if((code1=="ab" AND code2=="cd") OR (code1=="cd" AND code2=="ab"), "yes", "no")

[nanuli]

Hello @ITWhisperer . Thank you for the quick response. I have updated my post. There are multiple potential combinations--not just ab and cd--so the actual combination cannot be defined. 

[ITWhisperer · Accepted solution]

Always best to fully describe your problem giving as much sufficient detail as possible.

Try this

| eval codes=mvsort(mvappend(code1, code2))
| eventstats count by codes
| eval result=if(count==2, "yes", "no")

[nanuli]

Thank you so much, @ITWhisperer . That did the trick!