Map region in Canada by province

[Anonymous]

Hello,

I need help as I have some data by region in Canada;

West

East

Ontario

Quebec

I need to create a map for these regions for shipments. I have created a table with all province, with reference to the region;

ex: 

ProvinceRégion

Colombie-Britannique	West
Alberta	West
Manitoba	West
Saskatchewan	West
Ontario	Ontario
Quebec	Quebec
Nouveau-Brunswick	East
Nouvelle-Ecosse	East
Ile-du-Prince-Édouard	East
Terre-Neuve	East
Labrador	East

What I want to do is to create the Map with the propre region; East, but I want the choropleth map to be represented with all provinces included in the region. 

West would be

Colombie-Britannique	West
Alberta	West
Manitoba	West
Saskatchewan	West

The other issue is that I cannot create a relation without unique values in the relationship. I have created a "dummy" table showing just the West-East-Ontario-Quebec and have linked my data table to it and then that table to the provinces one.

Is there a way to get this done in PowerBI? Although I am not able to show any Map, (unless I just take one region for East and one region for West).

Thanks

[v-danhe-msft · Accepted solution]

Hi @Anonymous,

For the first issue, from your description, I guess you want to show the filter region in the map, right? If so, you could refer to below steps:

1.I have entered some sample data to test for your problem.

2.Create a Map visual and Create a Slicer visual. Now you can filter the region you want to show in the map by the slicer.

You can also download the PBIX file to have a view. If I misunderstand you, please let me know in time.

https://www.dropbox.com/s/ibxezk2quq8qywu/Map%20region%20in%20Canada%20by%20province.pbix?dl=0

For the second issue, Power BI is not supporting the many-to-many relationship currently. Power BI can only create relationships between 2 tables if at least one of the two selected columns hosts a distinct, unique list of the key values, that is why you need to create a dummy table which has the distinct value. You could refer to this blog: https://blogs.technet.microsoft.com/cansql/2016/12/19/relationships-in-power-bi-fixing-one-of-the-columns-must-have-unique-values-error-message/

Regards,

Daniel He