Find a list of names in a text field

[vqps8866]

Does anyone have any ideas on how I can accomplish the following task?

I have a list of sites and need to find the name of the site in a text field and, if found, retrieve its name.
I used a formula, but it slowed down processing.
Does anyone have any suggestions?

List in the List table.
The rawdata table has a description field where I will search for the name of the device from the list.

I am using this  formula:

Device Name =

VAR ListaSites = VALUES(List[List]) -- sua lista de sites

VAR Descricao = [Description]

VAR Resultado =

    CALCULATE(

        FIRSTNONBLANK(List[List], 1),

        FILTER(

            ListaSites,

            CONTAINSSTRING(Descricao, List[List])

        )

    )

RETURN

IF(

    ISBLANK(Resultado),

    "REQ",

    Resultado

)

Many Tks All

[vqps8866]

Hello Greg,

Your formula worked perfectly.
Thank you very much for your help.

[vqps8866]

Hello

How are you?

What happens is that I have a list of 3,000 site names and I need to look in a field called description, which contains various data and the site name as well.
I need to find which website name is in the field and write down the name that was found.

I even thought of something like this, but there are 3,000 names:

SWITCH(
TRUE(),
CONTAINSSTRING([device name], “AR-ACA-API-001”), “AR-ACA-API-001”,
CONTAINSSTRING([device name], “AR-AME-DISP”), “AR-AME-DISP”,
....

I am using another one but the processing time is too long:

Device Name =

VAR ListaSites = VALUES(List[List]) -- sua lista de sites

VAR Descricao = [Description]

VAR Resultado =

    CALCULATE(

        FIRSTNONBLANK(List[List], 1),

        FILTER(

            ListaSites,

            CONTAINSSTRING(Descricao, List[List])

        )

    )

RETURN

IF(

    ISBLANK(Resultado),

    "REQ",

    Resultado

)

Many tks for tyhe support.

[v-sgandrathi]

Hi @vqps8866,

Thank you for clarifying the requirement. Given that you have about 3,000 site names, using SWITCH or checking each row with CONTAINSSTRING would be quite inefficient. Instead, you can try a more efficient method with MAXX and ADDCOLUMNS to dynamically check for matches.

Device Name =
VAR __ListaSites = DISTINCT('List'[List])
VAR __Descricao = [Description]
VAR __Resultado =
   MAXX(
       FILTER(
           ADDCOLUMNS(
               __ListaSites,
               "__Found", CONTAINSSTRING(__Descricao, [List])
           ),
           [__Found] = TRUE()
       ),
       [List]
   )
RETURN IF(ISBLANK(__Resultado), "REQ", __Resultado)

Thank you.

[Azadsingh]

Hi @vqps8866 , Could you please eloborate a little and also help with sample column or data. Please try below formula if it works for you:

Device Name =VAR Descricao = RawData[Description]RETURNCOALESCE (CALCULATE (FIRSTNONBLANK ( List[List], 1 ),FILTER (List,CONTAINSSTRING ( Descricao, List[List] ))),"REQ")

Thanks,
Azad Singh Thakur
Power BI Enthusiast | LinkedIn: https://www.linkedin.com/in/thakurazad/

[Greg_Deckler · Accepted solution]

@vqps8866 That doesn't look right, CONTAINSSTRING takes 2 text parameters not a text parameter and a table. If you can provide sample data as text, this would be easier but maybe something like the following:

Device Name (Column?) =
  VAR __ListaSites = DISTINCT( 'List'[List] )
  VAR __Descricao = [Description] 
  VAR __Resultado = 
    MAXX(
      FILTER(
        ADDCOLUMNS( 
          __ListaSites,
          "__Found", CONTAINSSTRING( [List], __Descricao )
        ),
        [__Found] = TRUE()
       ),
       [List]
    )
   VAR __Result = IF( __Resultado = BLANK(), "REQ", __Resultado )
RETURN
   __Result