U2U Blog

for developers and other creative minds

Dynamics CRM 2013 and Power BI for Office 365 - Part 2

In the first part of this blog post we used Power Query to create the following Data Model in Excel. As you can see the relationship between the 2 tables is still missing and we would also like to define a Hierarchy in QueryFromContact. And although we could have done this in Power Query itself we would like to give our tables and columns more meaningful names. If you later want to publish this model to Power Q&A you also should define synonyms for your columns and tables but this is something that cannot be done if you have a regular version of Excel installed. Only the Excel versions that comes with Office 365 Professional Plus or the Click-to-Run version of Office 2013 Professional Plus have this capability.

Let's first clean up the QueryForContactSales table by renaming it to Sales. The ContactId field we would like to hide from the End User in the client tools. You can do this by right-clicking on it and selecting Hide from Client Tools.

Next we can right-click on the Sales table tab and give this table a decent description. This can also be done for each column you have in your table.

Let's rename the QueryForContacts table to Contacts. And rename Address1_City and Address1_Country to City and Country. If you have a version of Excel you also might want to define synonyms for your columns. Again hide ContactId from the client tools. The table now looks as follows.

Next we will make FullName the only default field and set ContactId as the row identifier. You can find these options when you press either the Default Field Set or Table Behavior button in the ribbon.

When the City column is selected Power Pivot does not yet know that it actually contains city names. You can see this in the screenshot that shows the Contacts table in the Data Category setting. When the Data Category is explicitly set to city Power Pivot knows that this field contains city information. This is important when we later want to create Power View Reports. We also need to verify that the Data Category for the Country column is set to Country (or Country/Region).

Now that we have cleaned up our two tables let's define the relationship between them and the hierarchy in Contacts. For this we which to the Diagram View. We drag ContactId from the Sales table to the Contacts table to define the relationship. Next we can right-click on the Contacts table to add the hierarchy. We name is Contact Geography. This hierarchy has the levels Country->City->FullName. Depending on which tools we want to support for end-user reporting we could hide the individual fields from the client tools like we did with ContactId. But if you want to use i.e. Power Map you have to leave the fields as is because Power Map does (currently?) not support hierarchies.

The final result looks as follows

Let's now turn back to Excel and add Power View Report to the workbook. A Power View report is created in the Excel Workbook by clicking the Power View button in the Insert ribbon tab.

Once Power View opens you will see the Power View report designer with the Power View Field List on the right. First we drag the Contact Geography hierarchy in the field list. Next we can add our 2 measures from the Sales table. We do not want to go to the sale of each customer however so FullName can be removed from the field list.

Each report you create in Power View initially starts as a table. Tables do not support hierarchies however. Only matrices do. We can switch to a matrix report by pressing the table button in the ribbon and selecting Matrix. In the Options section of the ribbon we could then use do enable Drill-Down/Drill up (via the Show Levels button) and show or hide the (sub) totals (via the Totals button).

In the image below we drilled into the country of Belgium and the totals where hidden. The up arrow can be used to go back to the Country level.

Drill up to country.

Although it's a little bit small we could add a Map to the left displaying the cities as Locations and Sales Amount in the Size field. The Map itself is tiled by the Country field. In the screenshot below a Column chart was added on the Country and Sales Count. Note that this chart also becomes a slicer for the 2 other report items. If you were to click on the bar for U.S. the Matrix and Map would only show U.S. data.

Our Excel workbook is finished and we will save it on the desktop of our local computer. If you have a on premise SharePoint 2013 environment this document could be uploaded to a Power Pivot Gallery.

In the third and final part of this blog post we will upload the Excel workbook to a Power BI site in Office 365.

Comments (27) -

  • Dewitt Sconiers

    6/24/2015 9:51:47 AM | Reply

    How to start a blog. Kindly suggest books on the subject?

  • Gertrud Legard

    6/26/2015 12:32:54 AM | Reply

    I have two computers: I call one the "good" computer -- it has two monitors. The other is my "junk" computer with one screen where I download a lot of stuff to it.. . If I wanted to continue using both computers but only with the dual monitors, what would I need to buy? Is there some sort of splitter I can buy that will allow me to switch between each CPU? Where can I buy one if it does in fact exist? Will I still be able to use one mouse and keyboard?.

  • technology

    6/28/2015 1:41:14 AM | Reply

    Can I just say what a relief to find someone who actually knows what theyre talking about on the internet. You definitely know how to bring an issue to light and make it important. More people need to read this and understand this side of the story. I cant believe youre not more popular because you definitely have the gift.

  • m88

    7/1/2015 5:54:36 PM | Reply

    Can I just say what a relief to find someone who actually knows what theyre talking about on the internet. You definitely know how to bring an issue to light and make it important. More people need to read this and understand this side of the story. I cant believe youre not more popular because you definitely have the gift.

  • internet resources

    7/13/2015 4:14:51 AM | Reply

    After study a few of the blog posts on your website now, and I truly like your way of blogging. I bookmarked it to my bookmark website list and will be checking back soon. Pls check out my web site as well and let me know what you think.

  • web index

    8/11/2015 10:32:09 PM | Reply

    Would you be interested in exchanging links?

  • web directory

    9/5/2015 11:00:37 PM | Reply

    I discovered your blog site on google and check a few of your early posts. Continue to keep up the very good operate. I just additional up your RSS feed to my MSN News Reader. Seeking forward to reading more from you later on!…

  • SIGINT

    9/13/2015 8:11:26 AM | Reply

    There is noticeably a bundle to know about this. I assume you made certain nice points in features also.

  • Babysitting jobs

    9/18/2015 2:37:11 AM | Reply

    I’d have to check with you here. Which is not something I usually do! I enjoy reading a post that will make people think. Also, thanks for allowing me to comment!

  • internet resources

    9/30/2015 7:51:54 AM | Reply

    Nice post. I learn something more challenging on different blogs everyday. It will always be stimulating to read content from other writers and practice a little something from their store. I’d prefer to use some with the content on my blog whether you don’t mind. Natually I’ll give you a link on your web blog. Thanks for sharing.

  • forex strategy

    10/4/2015 5:23:42 AM | Reply

    very nice post, i certainly love this website, keep on it

  • web directory

    10/14/2015 10:00:08 AM | Reply

    It’s hard to find knowledgeable people on this topic, but you sound like you know what you’re talking about! Thanks

  • green card lawyer

    10/21/2015 7:16:17 AM | Reply

    Spot on with this write-up, I truly think this website needs much more consideration. I’ll probably be again to read much more, thanks for that info.

  • seo directory

    10/22/2015 2:03:43 AM | Reply

    I’m impressed, I must say. Really rarely do I encounter a blog that’s both educative and entertaining, and let me tell you, you have hit the nail on the head. Your idea is outstanding; the issue is something that not enough people are speaking intelligently about. I am very happy that I stumbled across this in my search for something relating to this.

  • m88

    11/4/2015 9:01:31 PM | Reply

    Aw, this was a really nice post. In idea I would like to put in writing like this additionally – taking time and actual effort to make a very good article… but what can I say… I procrastinate alot and by no means seem to get something done.

  • FCPX Effects

    11/7/2015 11:39:25 AM | Reply

    I’d have to check with you here. Which is not something I usually do! I enjoy reading a post that will make people think. Also, thanks for allowing me to comment!

  • Motion 5 Templates

    11/7/2015 12:16:50 PM | Reply

    You made some decent points there. I looked on the internet for the issue and found most individuals will go along with with your website.

  • m88

    11/12/2015 6:57:41 AM | Reply

    very nice post, i certainly love this website, keep on it

  • cocuk sikisi

    11/28/2015 10:56:56 AM | Reply

    Your place is valueble for me. Thanks!…

  • Create your Online Store

    12/11/2015 10:53:37 AM | Reply

    You made some decent points there. I looked on the internet for the issue and found most individuals will go along with with your website.

  • Real Instagram Followers

    12/12/2015 8:22:51 PM | Reply

    There are some interesting points in time in this article but I don’t know if I see all of them center to heart. There is some validity but I will take hold opinion until I look into it further. Good article , thanks and we want more! Added to FeedBurner as well

Loading