Forum Discussion
Identify whether value in string matches values in another table
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.
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!!
3 Replies
- VahidDM
Super User
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!!
Thanks so much, really appreciate it!
- VahidDM
glad it helped
