Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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. String can be anywhere in the row not only at the beginning so in SQL %text_string%.
Is there any simple way to achieve aforedescribed?
Thanks
Solved! Go to Solution.
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)
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)
User | Count |
---|---|
98 | |
90 | |
77 | |
71 | |
64 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
67 |