Filter another table to see if any values from a concatenated list (using delimiters) are found

[Neiners]

I have a measure that returns a concatenated list of values and then I would like to use the results of the measure to see if any of those values are found in another table and return the results that were found

Measure result example,

Result 1, result 2, result 3, etc...

I then want to see if any of those results (result 1, result 2, result 3) are found in another table and then return which of those results were found. So if only result 2 was found, the measure would return "result 2" if result 1 and 2 were found, it would return "result 1, result 2"

[Bibiano_Geraldo]

hi @Neiners ,

lets suppose that:

• SourceTable – Contains a list of “results” (e.g., "Result 1", "Result 2", etc.) that you want to check against another table.

• OtherTable – Contains a list of “results” that represent the values you want to see if they exist in the source.

Note: In this sample, the common values between the two tables are Result 2 and Result 3.

Now, create a measure that checks each value in SourceTable to see if it exists in OtherTable and then concatenates the matching values.

MatchingResults =
VAR SourceValues =
    VALUES( SourceTable[Result] )
VAR FoundValues =
    FILTER(
        SourceValues,
        VAR CurrentResult = SourceTable[Result]
        RETURN
            CALCULATE(
                COUNTROWS( OtherTable ),
                OtherTable[Result] = CurrentResult
            ) > 0
    )
RETURN
    CONCATENATEX( FoundValues, SourceTable[Result], ", " )

now Create a Card visual on your report canvas and Drag the MatchingResults measure onto the visual.

You should see the result:

[Neiners]

For the "other" table the data may look like this

Column 1, column 2 (contcatenated list) for lookups

Item 1, result 2 result 4 result 6

Item 2, result 2 result 3 result 4

Item 3, result 1 result 2 result 4

So then for item 1 I want to see if result 2 or result 4 or result 6 are in the source table 

Item 2 I want to see if result 2 or result 3 or result 4 are in the source table 

Item 3 I want to see if result 1 result 2 or result 4 are in the source table

[v-qiaqi-msftv · Accepted solution]

Hi @Neiners,

Could you please share a bit more about your sample data?

For this lind of for all, a slight difference in the data structure will result in a completely different DAX.

I made a simple demo for your reference.

Not sure if it is what you want to achieve, please check for reference.

Check Results = 
VAR ItemResults = SELECTEDVALUE(ItemsTable[Results])  -- Get the single result string for the item
VAR ResultsList = SUBSTITUTE(ItemResults, ",", "|")   -- Replace spaces with a delimiter (pipe)
VAR ResultCount = COUNTROWS(VALUES('Table'[Result]))  -- Count of rows in the 'Table'
RETURN 
IF (
    ResultCount > 0 &&
    COUNTROWS (
        FILTER (
            'Table',
            CONTAINSSTRING(ResultsList, 'Table'[Result]) 
        )
    ) > 0,
    "Exists",
    "Not Exists"
)

Best Regards,
Qi
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[rajendraongole1]

Hi @Neiners  - This can be achieved by creating a DAX measure that takes the concatenated list of values from the first table and checks if they exist in another table, then returns the values that are found.

If you don't already have the measure to concatenate values from the first table.

ConcatenatedValues =
CONCATENATEX(
VALUES(Table1[Column1]),
Table1[Column1],
", "
)

Now create a second measure to check which of those concatenated values exist in another table

Please find the attached pbix file. Hope this helps.

[Neiners]

Using the example you provided, L2 is the one that has a concatented list of values that needs to be looked up against L1. So in L2, Column 2 row 1 may look like result 2,4,5 and then row 2 may look like result 1,3,5. I need to be able to check if row 1, result 2 or result 4 or result 5 are found in L1. For row 2, if result 1 or result 3 or result 5 are found in L1.