Forum Discussion
Modelling Custom Geographical Regions for Map Visual
Hi,
New to PowerBi and need help understanding how to model regions.
I'm working on creating a report that deals with multiple projects spread over several regions. Our company has custom geographical 'regions'. Some regions correspond to continents, while others are countries.
Dim Regions Table
| Region | Area Covered |
| EU | Europe |
| US | United States |
| JP | Japan |
| AS | Asia |
| AZ | Australia |
The data looks like this:
Fact Projects Table
| ProjectID | Project Title | Region | Department |
| 1 | Frodo | AZ, JP, EU | IT |
| 2 | Bilbo | JP, AS | Finance |
| 3 | Gandalf | EU, US, JP, AS, AZ | IT |
| 4 | Merry | US, EU, AZ | Operations |
So far, I've been able to split the region column in FactProjects table as follows:
| ProjectID | Project Title | Region1 | Region2 | Region3 | Region4 | Region5 | Department |
| 1 | Frodo | AZ | JP | EU | null | null | IT |
| 2 | Bilbo | JP | AS | null | null | null | Finance |
| 3 | Gandalf | EU | US | JP | AS | AZ | IT |
| 4 | Merry | US | EU | AZ | null | null | Operations |
My main 2 issues are these:
1. How do I "count" projects towards multiple regions?
2. How do I do a map visualsation with these custom regions?
Thank you for your help.
Hi,
In the Query Editor (where you split the Region column into multiple colukns), then is an option under Advanced to split by rows. After splitting by rows, create a relationship from the Projects to the Regions table. You should now be able to build your desired visuals.
8 Replies
Hi,
In the Query Editor (where you split the Region column into multiple colukns), then is an option under Advanced to split by rows. After splitting by rows, create a relationship from the Projects to the Regions table. You should now be able to build your desired visuals.
Thanks Ashish, really appreciate the help. I was able to split the data into rows, but the visuals still aren't working properly.
After splitting into rows:
After Splitting into Rows
Map visual with no slicer selections:
Map with no slicer selection
Map with Japan selected in slicer. The number of projects for Japan should be 4, not 1.
Slicer with Japan selected - the number of projects for Japan should be 4, not 1.
You are welcome. In the Query Editor column, right click on the Region column and trim the extra spaces.
ravgh , In power Query slip column by Delimiter into columns
https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/
I would provide the pbix file but apparently new members in this forum can't upload files here.
Note I've already created the realtionship, and also created a new measure for the Number of Projects card visual:
