Creating a geographical hierarchy : Continent > Country

[adrien5555]

Hi all,

Brand new on PBI, so please excuse these trivial questions 🙂

How to indicate that a column is for geo data, like countries? 

It indicates for some of my csv data sources, but not for others. 

Also, how to create a hierarchy of continent > country ? Is it by adding a column and an IF condition like (IF country is (US or United States) THEN 'North America' ?

Thanks a lot,

[ankitpatira]

@adrien5555 In powerbi desktop under Data view, select column, under modelling table, under Data Category dropdown you can select type.

Yes you can use conditional formatting option introduced in april powerbi desktop update to have continets for each countries (if you dont have large number of countries you can do manually for each). 

[adrien5555]

@ankitpatira : Great! Is there maybe a well-known formula to add a new column 'Continent' with the proper values, depending on a 'Country' column?

[ankitpatira]

@adrien5555 If you're familiar with DAX you can use LOOKUPVALUE function. What you can do is have a table with list of all the continents and countries (just google it i am sure you will find plenty). Import that. Create custom column and use LOOKUPVALUE function to update that column with contients with matching country names.

Let me know if you need hand and I can do a demo for you.

[adrien5555]

@ankitpatira : thx, really see the point here. Got a new dataset with continents and countries. 

Creating a Lookupvalue is a bit too much for me at this point... Any help very much appreciated. 

Is it like :

IF (country in dataset1 match country in dataset2) THEN (Field value is equals to corresponding value //continent// in dataset2)

?

Tried to create a relationship between other dataset with a 'country' field, but I got this :

 

[ankitpatira · Accepted solution]

@adrien5555 Actually there is an easy way. I replicated your scenario with Actual table (with values) and a table wth continents and countries (lookup table) and loaded it into powerbi desktop.

Go to query editor -> select values table -> Merge queries -> select matching columns from both table (countries column) -> left outer as join kind -> OK.

This will give you matching Continent from the lookup table

[adrien5555]

@ankitpatira: So cool thanks so much !

Having another problem to refresh the dataset, but the preview in the Query Editor looks just perfect.

thanks !

[asocorro]

In Data view, select the field and go here:

For hierarchies, just drag and drop fields on top of others in the Report view.