Passing a Filtered Table to SEARCH function

[Anonymous]

I have an InputTable table that has "~" in some rows in it's [Value] column. I want to remove / filter these rows and then pass the filtered InputTable table to a SEARCH function which will then do a partial match of the words in a single-column table WordList[Words] with the text in the InputTable[Value] column. Then find a Count of such matched values.

Right now SEARCH is doing a partial match on all the rows of the InputTable[Value] column which is increasing calculation time. I need to reduce time by excluding SEARCH from searching these ~ rows.

P.S: I want to do this using a DAX formula and not in Power Query.

VAR GT = COUNTA(InputTable[Value]) RETURN

IF(HASONEFILTER(WordList[Words]),

COUNTROWS( 
    CALCULATETABLE(
        InputTable
        , FILTER( InputTable, IFERROR(SEARCH(FIRSTNONBLANK(WordList[Words],1), InputTable[Value] ), -1)> 0 )
    )
)
,
GT
)

Thanks.

[v-frfei-msft]

Hi @Anonymous ,

Actually we can use KEEPFILTERS to work on it.

Measure 3 = var k = FILTER(InputTable,SEARCH("~~",InputTable[values],,BLANK())=BLANK())
return
CALCULATE(COUNTROWS(WordList),FILTER(WordList,WordList[Word] in VALUES(InputTable[values])),KEEPFILTERS(k))

 

Regards,

Frank

[Anonymous]

HI @v-frfei-msft ,

Getting an error for the "IN" operator for DAX in Excel 2016 PowerPivot. Here is the Sample Tool and Dummy Data files for your perusal.