Forum Discussion

mstefancik's avatar
mstefancik
Advocate IV
10 years ago
Solved

DAX equivalent of SQL LIKE

Hello, could anybody help me with DAX measure.   Lets say I have one Table with two columns:   TABLE: ID, Text   I would like to count allrows which includes certain string in Text field. Strin...
  • SamLester's avatar
    SamLester
    10 years ago

    The SEARCH function allows you to search for a specific string within another string. You can use a combination of CALCULATETABLE, DISTINCTCOUNT, and the SEARCH function, to count the number of occurrences:

     

     

    CntRows = CALCULATETABLE(ROW("rows",DISTINCTCOUNT(Table1[Text])),FILTER(ALL(Table1[Text]),SEARCH("text_string",Table1[Text],1,0)))

     

     

    There are more similar examples here:

    http://sqlblog.com/blogs/marco_russo/archive/2011/12/30/string-comparison-in-dax.aspx

     

    Thanks,
    Sam Lester (MSFT)

SamLester's avatar
SamLester
Microsoft Employee
10 years ago

The SEARCH function allows you to search for a specific string within another string. You can use a combination of CALCULATETABLE, DISTINCTCOUNT, and the SEARCH function, to count the number of occurrences:

 

 

CntRows = CALCULATETABLE(ROW("rows",DISTINCTCOUNT(Table1[Text])),FILTER(ALL(Table1[Text]),SEARCH("text_string",Table1[Text],1,0)))

 

 

There are more similar examples here:

http://sqlblog.com/blogs/marco_russo/archive/2011/12/30/string-comparison-in-dax.aspx

 

Thanks,
Sam Lester (MSFT)