How to map States with Dynamics CRM Accounts in Power BI Desktop

Following THIS previous post where I described how to connect Power BI Desktop to Dynamics CRM, we’re going to have a quick look at how to map our Accounts from Dynamics CRM in Power BI. For the scope of this post, we’re going to highlight the states where we have clients (Accounts).

Once we have the connection to Dynamics CRM established, we are presented with the Navigator. Here we can select the data from Dynamics CRM that we want to work with. I’ll be selecting here the AccountSet.

You will get a truncated view of the data for preview. Select Load. Note that this is not the fastest kid on the block, so wait for a moment while all your data loads. Depending on the data set, this could take a while.

Once loaded, your data set will appear in the Fields area, on the right hand side. If you want to make your query leaner, you could edit the properties and remove the columns you don’t need, but we’ll be looking at advanced query configuration some other time.

Now is probably a good time to check and make sure that your Accounts in CRM have Latitude and Longitude. Geo-mapping an address is not something you can turn on out of the box, so you will have to either customize the functionality to map an address to it’s coordinates, or use a 3rd party solution that does that.

Next select the icon that looks like a map, and click it. A map representation shows on the page.

Click on the Data icon on the left side. Here you get a view of the data from CRM, and the ability to customize each field’s properties.

Select each of the mapping fields and from the Properties ribbon area define the Data Category for each. For example, select the Address1_Longitude field and select Longitude in the Data Category.

Do the same for Latitude and the address fields as needed.

Now go back to the Report view and in the Visualization Fields wells start dropping the values you need as depicted below.

Now your Report view should refresh and show you something like this:

You can tweak the settings as needed. When satisfied, save and publish your report.

Enjoy!