Count occurrences within a field

[Ozcoz]

I'm encountering a challenge with counting occurrences of specific values in a field. In a column named STRATEGY, I currently have the following DAX measure to count occurrences:

 

STRATEGY_measure = CALCULATE( COUNTROWS(MNES), FILTER( MNES, MNES[STRATEGY] IN { "Value 1", "Value 2", "Value 3", "Value 4", "Value 5", "Value 6" } ) )

The provided DAX measure functions, however the count seems to consider only the first instance in each field and doesn't include subsequent values separated by a delimiter ("/"). For example, if a row has values "Value 1 / Value 2," the measure only counts "Value 1" and not "Value 2."

I've tried separating the values by the delimiter, but then thats a different formula which I couldn't work out.  What's the best to approach so that I can count all the occurences of a value?

Thanks.

[DataInsights · Accepted solution]

@Ozcoz,

One option is to split the column into rows using Power Query: Split Column --> By Delimiter --> Split into Rows.

You can simplify the measure as follows:

STRATEGY_measure =
CALCULATE (
    COUNTROWS ( MNES ),
    MNES[STRATEGY]
        IN { "Value 1", "Value 2", "Value 3", "Value 4", "Value 5", "Value 6" }
)