DAX Power BI occurrences of character in string

Question

-- my DAX formula is unable to count the occurrences of a "," (comma) in a string from Column2, in a specific "Category" row. --

Formula = CALCULATE( COUNTAX( FILTER ('Query1', 'Query1'[Col1] = "Category", 'Query1'[Col2] = ",") ) )

-- Any suggestions? --

Answer 1

You can use the following coulmn calculation to count the number of comma in a string:

Check = LEN(Query1[Col2])-LEN(SUBSTITUTE(Query1[Col2],",",""))

If you need the overall count, you can simply sum up the calculated field. Hope this helps.

Answer 2

This should do the trick without the need of a calculated column

Formula :=
CALCULATE (
    COUNTROWS ( FILTER ( 'Query1', FIND ( ",", 'Query1'[Col1],, 0 ) > 0 ) ),
    'Query1'[Col2] = "Category"
)