How do I cross-reference datasets to create new calculated fields?

[Nerb]

I'm trying to use powerBI to build a cloropleth map that shows lending across US counties, but I want to use census data to adjust that data so that what is shown is a the total ammount of lending as a proportion of the population for each county (per capita).   

To do this I have I have two datasets (three if you include the topoJSON):

1. My business data - which shows individual loans with unique keys and includes a "County" Column.

2. Census data - Which is a simple summary (i:e: County | Sum of Population)

3. The TopoJSON, which has a County field.

I've standardized the Fields in the County columns across all three datasets so they're identical, but now I'm trying to connect the thre datasets so that I can generate a per capita measure that I can visualize. What's the best way to go about doing this?

This is easy to do in excel with a pivot table, but I'm not sure how to go about building it into my visual for PowerBi.

Is the best way to do this simply to generate an additional dataset that combines tables 1 & 2 and then use that new table for the visualization? 

[v-frfei-msft]

Hi @Nerb,

Based on my test, you cannot combine two datasets directly. Here I suggest you to import related data sources and create relationship between tables to genereta a new dataset so that you can achieve your goal.

Regards,

Frank

[Greg_Deckler]

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490