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.
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.
Solved! Go to 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.
Note I've already created the realtionship, and also created a new measure for the Number of Projects card visual:
I would provide the pbix file but apparently new members in this forum can't upload files here.
@ravgh , In power Query slip column by Delimiter into columns
https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/
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:
Map visual with no slicer selections:
Map with Japan selected in slicer. 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.
This worked! The rows needed to be trimmed, as you said. Thank you
You are welcome.
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.
User | Count |
---|---|
108 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |