Trying to replicate COUNTIF in DAX to calculate a Measure in Pivot Table. I want to know which of SUMX or CALCULATE is more efficient?

Question

I want to calculate number of records on 'Data Dump' table where [Stage] column contains the value "Enquiry". I would also like to understand the difference in execution of these formulas.

I have used the below DAX formulas to evaluate - which formula is more efficient?

=SUMX('Data Dump',MIN(SEARCH("Enquiry",'Data Dump'[Stage],,0),1))

=CALCULATE(COUNTROWS('Data Dump'),SEARCH("Enquiry",'Data Dump'[Stage],,0)>0)

=CALCULATE(COUNT('Data Dump'[Stage]),SEARCH("Enquiry",'Data Dump'[Stage],,0)>0)

=CALCULATE(COUNTROWS(FILTER('Data Dump',SEARCH("Enquiry",'Data Dump'[Stage],,0)>0)))

Also, would it be more efficient if I simply add formula columns to my data source table with 1 and 0 for different criteria (eg. value is "Enquiry", "Pipeline", "Converted", etc) and then sum it up to get total count?

Below are the results obtained by running in DAX Studio, performance is similar as far as I can identify.

Answer 1

I would recommend one of the following:

CALCULATE (
    COUNTROWS ( 'Data Dump' ),
    CONTAINSSTRING ( 'Data Dump'[Stage], "Enquiry" )
)

or

COUNTROWS (
    FILTER (
        'Data Dump',
        CONTAINSSTRING ( 'Data Dump'[Stage], "Enquiry" )
    )
)

I'd expect these to be similar in efficiency but you'd need to actually test to know for sure.