DAX equivalent to =SUMPRODUCT(ISNUMBER(SEARCH)?

[Mat87]

Hey all

Does anyone know how to get this formula in DAX? I want the output in column C.

I think I should use SUMX instead of SUMPRODUCT but the formula is giving me error. 

[Phil_Seamark · Accepted solution]

You could create a table like this

Table = 
var KeyWords = SELECTCOLUMNS('KeyWord',"KeyWord",'KeyWord'[Keyword])
var CellValues = SELECTCOLUMNS('KeyWord',"Cell Value",[Cell Value])
var ReturnTable = FILTER(
    CROSSJOIN(KeyWords,CellValues) , SEARCH([KeyWord],[Cell Value],,0) > 0)
return ReturnTable

Then create a measure on the table  that counts the Keyword column