Measure to count iteration of specific character string

[Fab117]

Hello,

I'm newbie with Power BI.

I'm quite familiar with VBA, but not at all with Dax.

I started building my first Power BI report based on several data base fields, but would need to do some calculation with Dax.

I spent time with Microsoft Dax functions definitions (Data Analysis Expressions (DAX) Reference - DAX), but didn't find my solution.


Challenge n°1: Measure to count iteration of specific character string

In database "Projects", I've a column [AreaImpacted] where I can find market( s) impacted on each line of the database. NB: There can be one or several markets per line (if several, separator is ",").

For each market (hopefully, number is limited), I'd like to create a specific measure calculating the number of iteration (no matter if the market is alone or not on the line looking at)

e.g.:

AreaImpacted

USA

USA, France, Canada

Germany

France

Canada, USA

Results should be:

#USA = 3​

#France = 2​

#Canada = 2​

#Germany = 1​

Do someone could help me with the formulae's?

#USA =
CALCULATE (

…​

)

Thanks a lot


Fab

 

[Fab117]

In case someone else would face same challenge:

Germany = CALCULATE(COUNT('Table'[AreaImpacted]),CONTAINSSTRING('Table'[AreaImpacted],"Germany"))

Thanks to v-stephen-msft