Forum Discussion

Ozcoz's avatar
Ozcoz
Frequent Visitor
2 years ago
Solved

Count occurrences within a field

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.

Need Help
  • DataInsights's avatar
    DataInsights
    2 years ago

    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" }
)

     

1 Reply

  • DataInsights's avatar
    DataInsights
    Icon for Super User rankSuper User
    2 years ago

    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" }
)