Modelling Custom Geographical Regions for Map Visual

[ravgh]

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.

[ravgh]

Note I've already created the realtionship, and also created a new measure for the Number of Projects card visual:

[ravgh]

I would provide the pbix file but apparently new members in this forum can't upload files here.

[amitchandak]

@ravgh , In power Query slip column by Delimiter into columns

https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/

[Ashish_Mathur · Accepted solution]

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. 

[ravgh]

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.

[Ashish_Mathur]

You are welcome.  In the Query Editor column, right click on the Region column and trim the extra spaces.

[ravgh]

This worked! The rows needed to be trimmed, as you said. Thank you

[Ashish_Mathur]

You are welcome.