How to use LOOKUPVALUE (I think) to return result

[Anonymous]

Hi.  I'm trying to populate a new column in my main table with a keyword pulled from another table. I think the formula I want is a simple LOOKUPVALUE formula but it's not working:

• My main table contains, among other things, an incident short description - that table is "Incidents".
• The table that holds the possible keywords is called "Conference" and has three columns called Return, Look In Field (which I have structured as the column name from the Incidents table:  'Incidents'[_Inc Lowercase Short Desc], and Search For Term.
  • The Search For Term column has a variety of strings that might exist in the Short Description (i.e. Look In Field) such as activat (to cover occurences of activation or activate or deactivate), pro, button, add-in, etc.

I need a formula that says "at the first Search For Term string found in Look In Field, populate the new column with Return".  The following, though, fails:

_Conf Keyword Test = LOOKUPVALUE('_Lookup - Conferencing'[Return], '_Lookup - Conferencing'[Look In Field], '_Lookup - Conferencing'[Search For Term])

with this message:  A single value for column 'Search For Term' in table '_Lookup - Conferencing' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

I think I need Search For Term to be a variable but I'm not sure.  What should the formula be?  

Thanks in advance.

[AlexisOlson]

Can you give some example tables and expected results? I don't quite follow what you're asking.

[Anonymous]

Main Incident table:

Inc Number                                              Short Description                                   Desired Keyword

INC1234                                                      please reactivate account                  

INC2345                                                      the icon has disappeared                  

INC3456                                                      i need the professional version       

INC4567                                                      my account is not working                

INC5678                                                      can't cancel my meeting                    

Lookup table:

Search Term 1                                          Search Term 2                                         Keyword to return

activat                                                                                                                              Activation Status

icon                                                                                                                                   Icon/Button

pro                                                                                                                                    Upgrade License

cancel                                                          meet                                                           Cancel Meeting

What I need is for the "Desired Keyword" column in the Incident table to be populated with the "Keyword to return" value if "Search Term 1" and, if populated, "Search Term 2" exist in the "Short Description" column.

So the end result would be:

Inc Number                                            Short Description                                Desired Keyword

INC1234                                                 please reactivate account                    Activation Status

INC2345                                                 the icon has disappeared                    Icon/Button

INC3456                                                 i need the professional version           Upgrade License

INC4567                                                 my account is not working              

INC5678                                                 can't cancel my meeting                      Cancel Meeting

I've been working at this for 2 days and can't find the answer.  LookupValue offers the ability to have multiple criteria but it fails.  I found a formula that uses MINX combined with Filter and Search but it returns multiple Desired Keywords if more than 1 word is found (and I can't figure out how to separate the results so I get things like "Activation StatusIcon/Button" or whatever.

I've been using formulas like this:

IF(SEARCH("activat",Incidents[_Inc Lowercase Short Desc],1,0),"Activation status",

IF(SEARCH("disable",Incidents[_Inc Lowercase Short Desc],1,0),"Activation status",........

but I have a couple hundred Search Terms to look for so a formula that is a couple hundred lines long isn't practical.

Please, please help!

[AlexisOlson · Accepted solution]

LOOKUPVALUE does *not* support partial matches, so I'd suggest an approach using CONTAINSSTRING instead.

Try this as a calculated column on the Incidents table:

Desired Keyword =
MAXX (
    FILTER (
        Keywords,
        CONTAINSSTRING ( Incidents[Short Description], Keywords[Search Term 1] ) ||
        CONTAINSSTRING ( Incidents[Short Description], Keywords[Search Term 2] )
    ),
    Keywords[Keyword to return]
)

[Anonymous]

Ok, one more level of complexity if possible:  Is there a way to exclude terms - maybe by adding one or two columns called Exclude Term? 

So if Search Term 1 is "update" and Exclude Term 1 is "password" and Keyword to Return is Upgrade License, only those entries with "update" that don't contain "password" would get assigned the keyword.

[AlexisOlson]

Sure, you can add additional logic to the filter condition.

Instead of the logic being (written more condensed):

Contains Term1 || Contains Term2

you can write

( Contains Term1 || Contains Term2 ) && NOT ( Contains Exception1 || Contains Exception2 )

[Anonymous]

OK, that's it!  I'm just going to go sit in the corner and cry.  😢

I thought we had it but we don't.  The formula is not returning the first matching value...in fact, I can't understand why it's picking the value it does.

If the Short Description says "Can't record meeting...asking for password", it should return a keyword of "Recording" because the Search Term 'record' is #31 in the lookup table, 'password' is #39 but what it's returning is "other" which is #77.

The articles you gave are good but will require some time to absorb and I need to get this working sooner rather than later.  😞

Ideas?

[Anonymous]

Alexis,

After working with the formula over the weekend, I've found that while it does exactly what I want it to, it is not terribly effecient.  If I make even the most minor adjustment to it (like adjust the spacing), it recalculates.  That recalculation takes an extremely long time, comparatively - on the order of about 15 minutes.  My suspicion is that it is running through all the search term rows (in this case, 121 of them) and comparing each one to the 500K+ of data.  In essence, performing over 60M calculations.  

Since the Terms are already in the desired order of application, what I'd like is for it to compare the terms in the order I set them and stop after the first match, then move on to the next Incident row.

Am I understanding what it is doing correctly and if so, is there a way to make it more effecient?

[AlexisOlson]

There are things you could try but optimization tends to involve testing against actual data, so I can't easily forecast what would help significantly and what wouldn't.

Here are some articles that may give you ideas of things to try:

https://www.sqlbi.com/articles/optimizing-if-conditions-using-variables/

https://www.sqlbi.com/articles/understanding-eager-vs-strict-evaluation-in-dax/

https://www.sqlbi.com/articles/optimizing-conditions-involving-blank-values-in-dax/

https://www.sqlbi.com/articles/optimizing-if-and-switch-expressions-using-variables/

https://www.sqlbi.com/articles/optimizing-duplicated-dax-expressions-using-variables/

[Anonymous]

I had to use an OR clause ( || ) to say 'if does not contain or is blank' to get it to work.  I also changed it to MINX so it would grab the first match and added a couple of IF statements.  Here is the final formula for anyone who might be interested:

I can't thank you enough Alexis!!!!  You are my hero!

[Anonymous]

You ARE a rock star!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!