Azure Maps Power BI: Slice Business Central Data

You are a Power BI user and you want to see a map with Microsoft Dynamics 365 Business Central customer locations and a graphical indication as to the volume of their sales. You also want to slice and compare the sales data by year and month.

The Azure Maps Power BI Visual gives you a platform to deliver a solution that meets your needs. You can create a simple Power BI report that shows a map of customers and allows you to select combinations of years and months with the slicer on the right. Let's go ahead and create this solution in Power BI.

Customer Sales Data with Power BI Slicer

1. First you need to have an Azure Maps Account and have the feature turned on in Power BI by your tenant administrator. Then, click on the Azure Map visual under visualizations in Power BI. You will see a disclaimer if everything has been set correctly.

Azure Maps Power BI Visual

2. We need to turn off the Auto date/time feature under the Options / Current File / Data Load section.

Time Intelligence Auto date/time option

3. Let's get the data for this solution. In the lower menu click on Get data and get the Business Central customers and itemLedgerEntries tables under the Standard APIs v2.0 folder.

Select the Standard APIs v2.0 Tables

4. We need to add a new column to the customer table that we will assign to the map visual. Click on customers in the Data pane. Then in the lower menu click on New column and then enter the following:

fullAddress = customers[addressLine1] & ", " & customers[city] & ", " & customers[state] & " " & customers[postalCode]

5. We need to create a Measure that will filter item ledger entries and sum them. Click on Customers in the Data pane. Then in the lower menu click on New measure and then enter the following:

itemSales = CALCULATE(SUM(itemLedgerEntries[salesAmountActual]),itemLedgerEntries[sourceType]="Customer")

6. Next, we will create a date table with a custom hierarchy. Click on Modeling in the upper menu and then New table in the lower menu. Then enter the following:

calendar = CALENDAR(FIRSTDATE(itemLedgerEntries[postingDate]),LASTDATE(itemLedgerEntries[postingDate]))

7. We will now create the hierarchy component in the date table. Click on the calendar table in the Data pane.

a. Click on New column in the lower menu and enter:

Month = MONTH('calendar'[Date])

b. Click on New column in the lower menu and enter:

Year = YEAR('calendar'[Date])

c. Right mouse on the Year column under the calendar table and click on Create hierarchy.

d. Right click on the Month column and then click on Add to hierarchy. Select Year Hierarchy.

e. Right mouse on Year Hierarchy and rename the column to Calendar Hierarchy

8. We now want to create relationships between the tables. Create the following two relationships after clicking on the Model view on the left of the screen.

a. calendar (Date) to itemLedgerEntries (postingDate)

b. Customer (number) to itemLedgerEntries (sourceNumber)

9. We now want to set the fields for the Azure Map Visual we created in step number one. Click on the Azure Map Visual you created earlier.

a. Click on the checkbox next to the fullAddress column in the customer table in the Data pane.

b. Click on the checkbox next to the itemSales column in the Customer table in the Data pane.

10. Let's add a Slicer for the Calendar Hierarchy. Click on the canvas and then on the Slicer under the Visualizations pane. Then click on the checkbox next to the Calendar Hierarchy column under the calendar table. You should now see the years listed in the Slicer Visual.

Select the Calendar Hierarchy Option

11. You now have the ability to select years or months when viewing your Azure Map Visual.