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

[zack]

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. 😉

[v-shex-msft · Accepted solution]

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

[paulag]

Is the second file the watch list?

[zack]

Yes.