Measure to count iteration of specific character string

Hello,

I'm newbie with Power BI.

I'm quite familiar with VBA, but not at all with Dax => PowerQuery requests.

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 | Microsoft Learn), 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.:

Fields name_________AreaImpacted

Line 1________________USA

Line 2________________USA, France, Canada

Line 3________________Germany

Line 4________________France

Line51________________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