How to return a list of rows from a table where text is found within a free text field in a/o table

[marktait]

I have a table, Defender, with a column, Device Name

I have another table, Defender SNOW, which has a column, Short Description (which is a text field).

The Short Description contains free text. I want to be able to lookup 'Defender'[Device Name] and check if it appears anywhere in the 'Defender SNOW'[Short Description] field. 

I can do this with the following calculated column:

Has SNOW Ticket = 
    IF(
        ISEMPTY(
            FILTER(
                'Defender SNOW',
                CONTAINSSTRING(
                    'Defender SNOW'[Short description],
                    Defender[Device Name]
                )
            )
        ),
         FALSE(),
         TRUE()
    )

I can then lookup the 'Defender SNOW'[Number] column to return the reference number of the record where the device name exists.

Has SNOW Ticket Ref = 
    IF(
        ISEMPTY(
            FILTER(
                'Defender SNOW',
                CONTAINSSTRING(
                    'Defender SNOW'[Short description],
                    Defender[Device Name]
                )
            )
        ),
        BLANK(),  -- or you can use 0 or any default value based on your requirement
        MAXX(
            FILTER(
                'Defender SNOW',
                CONTAINSSTRING(
                    'Defender SNOW'[Short description],
                    Defender[Device Name]
                )
            ),
            'Defender SNOW'[Number]
        )
    )

However, I want to be able to return multiple entries from the 'Defender SNOW' table, if the [Device Name] is found in multiple records in the [Short Description] field, and show the multiple 'Defender SNOW'[Number] in a table on a Power BI tab.

Is it possible to do this?

Alternatively, is it possible to create a dynamic table, which is will list all records in the 'Defender SNOW' table where 'Defender'[Device Name] exists in Short Description? I could then just show that dynamic table in a visual/power BI tab.

So, for example, the source tables and the expected/desired resulting dynamic table would look like this:

Thanks for any pointers,

Mark

[marktait · Accepted solution]

Hi - adding ths measure works (thanks to Sam Nseir):

SNOW count = 
  CALCULATE(
    COUNTROWS('Defender SNOW'),
    CONTAINSSTRING('Defender SNOW'[Short Description], SELECTEDVALUE('Defender'[Device Name]))
  )

Thanks, Mark

[Anonymous]

Hi @marktait ,

In order to better understand your demands and give the right solution, could you please provide some more specific information? such as your desensitized example data and a screenshot of your desired results?

Thanks for your efforts & time in advance.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[marktait]

Hi - of course - I've added sample tables and the desired result to the original post. Thanks.