Forum Discussion

zack's avatar
zack
Frequent Visitor
9 years ago
Solved

Create column based on occurence of string value contained in lookup table

I need to show how many transactions have payees from a watch list. There are two files, the monthly transaction file and a watch list containing essentially one field.

 

  • The primary file contains ~80k records each month, with one field labled "Description" that includes payee info. There's no real pattern.
  • The second file is a lookup table containg about 20 records with a field labled "Name" that contains the word or words I want to scan for.
  • The result should be a new column in the primary file with a value that is either true or false, or contains the word that matched. (i'm not sure which is better).

 

I don't know where to start. I have a basic understanding of M and DAX. I know how to use the Advanced Editor. But I'm no programer. just a hack. ;-)

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi zack,

     

    You can use contains function to check if the value is contained in search table.

     

    Measure:

     

    IsContains = 
var currItem= LASTNONBLANK('CurrentTable'[ColumnName],[ColumnName])
return
CONTAINS(SearchTable,SearchTable[ColumnName],currItem)

     

     

    Then drag above measure and the watch item column to a table visaul.

     

    Regards,

    Xiaoxin Sheng

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi zack,

     

    You can use contains function to check if the value is contained in search table.

     

    Measure:

     

    IsContains = 
var currItem= LASTNONBLANK('CurrentTable'[ColumnName],[ColumnName])
return
CONTAINS(SearchTable,SearchTable[ColumnName],currItem)

     

     

    Then drag above measure and the watch item column to a table visaul.

     

    Regards,

    Xiaoxin Sheng