Forum Discussion

brourmind's avatar
brourmind
New Member
4 years ago
Solved

Convert Excel formula to DAX Measure

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's avatar
    tamerj1
    4 years ago

    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]
)

4 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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's avatar
      brourmind
      New Member
      4 years ago

      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's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        4 years ago

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