How to extract string, and find the values attached to it?

[Ghuiles]

Hello

I got a list with some incidents. The current table, coming from Excel looks like:

Country      Location  KPI  Location count

DE               A              10  1

CH, RU, AT  All           100 ????

In the table Location i know that

DE has max 8 locations

CH has max 2 locations

RU has max 10 locations

AT has max 2 locations

How could i calculate in Power BI (in M or DAX)  the location count for the second row and its split by country? 

I would expect CH+RU+AT=2+10+2=14.

The goal is of course aftwerwards to show per country how many incidents occurred.

The column country can countain one or several countries in it. Not necessarily 1 or 3 but more.

Thanks in advance. Cheers. G.

[Zubair_Muhammad · Accepted solution]

@Ghuiles

Try adding a custom column like

=Text.Split([Country],",")

Then expand it to new rows to get each country in separate row

Then you can merge it with Location table

[Ghuiles]

@Zubair_Muhammad 1000 thanks!!
If i knew it was so simple... Gee

Cheers

[Ghuiles]

by the way, do you know a trick to generate rows the same way when the country is for example "All Europe"?

I mean, in the Excel file i have got, sometimes the country is "All Europe" or "All Asia". I could maintain a table that lists what is Europe and what is Asia, but how to make the link between these both tables?

I first wanted to do this with DAX, but i could be cool if M would allow that.

Cheers. G.