Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Do someone could help me with the formulae's?
#USA =
CALCULATE (
…
)
Thanks a lot
Fab
Solved! Go to 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.
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.
Hi @v-stephen-msft ,
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
think it's better to split the column into rows with M code.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.