Convert Excel formula to DAX Measure

[brourmind]

Hi,

How can i convert below Excel formula to Power BI measure to search a keyword from a description.

=IFERROR(LOOKUP(99999,SEARCH(issue[Keywords],[@[Short description]]),issue[Grouped Issue]),"Others")

> issue[Keywords] is list of keywords in table "issue"
> @[Short Description] is the where I want to search the keyword from, in another table
> issue[Grouped Issue] is the return if "Keywords" found from "Short Description"

i tried using below measure in Power BI

issue = LOOKUPVALUE( 'Table'[Grouped Issue], 'Table'[Keywords], 'inc_vol'[Short description] )

but the results not satisfactory or inaccurate, it returns me with below 200 records while it should return with over 1k records.

Help is very much appreciated. Thank you.

[tamerj1 · Accepted solution]

Hi @brourmind 

is this a calculated column in the 'inc_vol' table? If so please try

issue =
MAXX (
    FILTER (
        'Table',
        CONTAINSSTRING ( 'inc_vol'[Short description], 'Table'[Keywords] )
    ),
    'Table'[Grouped Issue]
)

[brourmind]

Hi @tamerj1 ,

It's worked to some extend but not entirely, it also return those with no keywords available. 

e.g. when the group contain "cert" or "certificate" for keywords, but in short description those does not have those keywords listed as well.

[tamerj1]

@brourmind 
You need to provide sample data with that summarizes all cases as more extensive text processing might be required.

[brourmind]

UPDATE: Thank you for the call @tamerj1 . 

In conclusion, I need to add more accurate information to the list of keywords.

----

i can't show everything but I filtered out those common in "inc_vol" that does not have the keywords from "table" yet. As you can see, i do not have anything related to "password" keywords under "Business App".