Finding a match in multiple fields

[Creative_tree88]

Hi all - I've attached some sample data which I'm hoping you'll be able to help me with!  There are essentially 2 tables.  The first one looks at activity and pulls everything associated with each USER_ID (common key amongst both tables).  These users are each permitted to refer specific 'modalities' - we have thousands of users!  The modalities each user is permitted to refer to is located in the table 2.

The problem is, that each user will refer multiple modalities but all appear on one line of the dataset.  So, I need to be able to look at table 2 for each line of data and determine whether each user is permitted to refer the modality which is present in table 1.  If they are, it needs to say something like 'OK'.  If not, then 'NOT VALID'.

Once I've done this, I can then set up a specific filter based on this new OK / NOT VALID field, so that my report can be filtered to just display those records which are deemed NOT VALID - so that some further investigation can occur.

The desired result is displayed to the right of table 1 and table 2, in the sample data.

Hopefully someone can help with this.  Many thanks!

Sample Data 

[Creative_tree88]

@v-pgoloju That looks pretty amazing to me!  Thanks so much, really appreciated.

[v-pgoloju · Accepted solution]

Hi @Creative_tree88,

Thank you for reaching out to the Microsoft Fabric Forum Community.

Please try the fallowing dax to create calculated column in activity table.

Validity Check =
VAR UserID = Activity[USER_ID]
VAR Modality = Activity[MODALITY]
VAR PermittedModalities =
", " & SUBSTITUTE(
LOOKUPVALUE(UserModalities[MODALITIES], UserModalities[USERID], UserID),
",", ", "
) & ","
VAR ModalityCheck = ", " & Modality & ","
RETURN
IF (
SEARCH(
ModalityCheck,
PermittedModalities,
1,
0
) > 0,
"OK",
"NOT VALID"
)

I’ve attempted to recreate the scenario using sample data.

If you find this response helpful, please consider marking it as the accepted solution and giving it a thumbs-up to support others in the community.

Thank you & regards,
Prasanna Kumar