In Power BI, how to return a list of rows from a table (or create a dynamic table) where text is found within a free text field in another table

Question

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.

(In SQL, I would use a Select....where 'Short Description' Like '%[Device Name]%')

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

Answer 1

Create a new Measure with:

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

Then add this new measure to your Table visual.

Note you will get a visual error complaining that a relationship couldn't be determined - this will disappear as soon as you add the measure to your visual.