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 => 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

[Anonymous · Accepted solution]

Hi @Fab117 ,

I'll first give you a popular science about the difference between M language and DAX language. In Power Query, we'll use M. DAX, which we use in Power BI Desktop.

Here's the solution in Power BI Desktop, which means I used DAX to complete.

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

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

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

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

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Fab117]

Hi @Anonymous ,

Thank you very much for taking time looking at my challenge and for this solution which fit perfectly my needs.

Have a nice week.

Fab

[wdx223_Daniel]

think it's better to split the column into rows with M code.

[lbendlin]

Please provide sanitized sample data that fully covers your issue.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523