Forum Discussion

Creative_tree88's avatar
Creative_tree88
Helper V
1 year ago
Solved

Finding a match in multiple fields

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 

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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