Identify whether value in string matches values in another table

[lilpumpkini]

Hi

I have a table called Group that has a concatenated list of Div Codes in a field called Open Divisions

Client	Open Divisions	In Scope
1	EM	Yes
2	AB, DE, PO, LM	Yes
3	IJ, MN, ST	No
4	CG, XY, LM	Yes

I have another table called In Scope that has a list of Div Codes

In Scope Table

Div Code

EM

AB

FB

CG

I want to create a calculated column on the Group table where if one of the div codes listed in the string of "Open Divisions" matches any of the div codes in the Div Codes column of the In Scope Table, it should say Yes, Otherwise No.

[VahidDM · Accepted solution]

Hi @lilpumpkini 

Use this DAX to create that calculated column:

In Scope Check = 
VAR OpenDivisionsList = SUBSTITUTE([Open Divisions], ", ", "|")
RETURN
IF(
    COUNTROWS(
        FILTER(
            'In Scope',
            SEARCH('In Scope'[Div Code], OpenDivisionsList, 1, 0) > 0
        )
    ) > 0,
    "Yes",
    "No"
)

Output:

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

Appreciate your Kudos!! 

LinkedIn | Twitter | Blog | YouTube 

[lilpumpkini]

Thanks so much, really appreciate it!

[VahidDM]

glad it helped