As most of you guys know Power BI Desktop is released. I should say, it’s awesome. There are heaps of changes in compare with its preview edition Power BI Designer. I’ve written a series of posts regarding creating a report and dashboard using Power BI Designer before. You can find them here. Now I want to explain the same thing in Power BI Desktop. I’ll cover lots of new features in this post and I hope you enjoy it.

Get Data

• Open Power BI Desktop
• Click on Get Data. You can also get data from recent data sources or even open a predefined report stored in pbix format

• We use Adventure Works DW 2012 database as sample, you can open your real world data source
• Click on “SQL Server Database” then “Connect”
• In this sample we are connecting to a “SQL Server Database”
• Click “Connect”

• Enter the server and database names. The database name is optional here so you can leave it blank. However, You’ll need to select it in the next pages. So in this sample I enter the database name. You can also write your own custom SQL statement. Click OK.

• If you’re using a SQL Server user then tick “Use alternate credentials” and enter the corresponding username and password. Otherwise just click connect

• In this sample we’re going to analyse reseller’s sales. So we select the following tables from the database
  1. 1. 1. FactResellerSales
        2. DimCurrency
        3. DimDate
        4. DimEmployee
        5. DimGeography
        6. DimOrganization
        7. DImProduct
        8. DimProductCategory
        9. DimProductSubcategory
        10. DimPromotion
        11. DimReseller
        12. DimSalesTerritory
• Click Load

• Now you should see selected tables on the “Fields” pane

So far we got the data we need to analyse. Now we should go to the next step.

Make Names More User Friendly

This part is going to be a very easy, but, time consuming part of our work. The more objects you have the more time you need to take on this part.

Rename Queries and Fields

In Power BI Desktop you can rename the queries and their fields from 3 different places.

1. From report view
   • Select the report view from the left pane
   • Right click on the query or field from the “Fields” pane and select “Rename”
   • Rename the object
2. From Data view
   • Click on “Data” view from the left pane
   • To rename the query you can right click on the query from “Fields” pane then “Rename”
   • To rename the fields you can either expand the query, right click on the field then “Rename” OR you can simply click a query from “Fields” pane then right click on the column and “Rename” it
   • If you are a keyboard guy like me, you can press F2 on the queries or fields from “Fields” pane to rename them as well.
3. From Query Editor
   • It doesn’t matter in which view you are, just click “Edit Queries” from the ribbon
   • Select the query
   • For renaming the query you need to rename it on “Query Settings” pane then press Enter
   • To rename the fields right click on the column then rename
   • After renaming you’ll see that another step is applied to the query. You can see the codes behind the seen by clicking on “Advanced Editor”

Hide Queries and Fields

To hide a query or a field you can do the same as we did for renaming the queries or fields. You just need to select “Hide” when you right click on the object from the report view.

Or select “Hide in Report View” when you do the same in the report view.

Unfortunately, we need to hide the corresponding fields or queries one-by-one as multi-select is NOT available here that makes it more time consuming.

NOTE: You can NOT hide fields from Query Editor.

UPDATE: Multi-select is available now with the new release of Power BI Desktop (Ver. 2.26.4128.403) in “Relationships” view:

• Multi-select desired columns
• Right click on the columns
• Click “Hide from “Hide in Report View”
• 

It’s much easier now to hide unneeded columns from the reports.

Managing Relationships

You can manage relationships by clicking on the “Manage Relationships” button available on the ribbon.

As you can see in the screenshot there are some inactive relationships between the tables. This is because there is another relationship between those two tables. For instance, order date and ship dates links are inactive as there is an active relationship between “Reseller Sales” and “Date” tables. The “Due Date” link between “Reseller Sales” and “Date” tables is activated by default so we are not able to activate the other relationships.

But, what should we do in these cases? Previously I explained how to implement role-playing dimensions in SSAS Tabular model . The same concept applies to Power BI Desktop so I won’t explain it here again. So we need to import the date dimension two more times to our Power BI Desktop model. Then we need to create new relationships between the imported date tables and the Reseller Sales table. As I created DueDate, OrderDate and ShipDate views on SQL Server  before I just need to import them to my Power BI Desktop model. To do so you need to “Get Data” then make names more user friendly.

Click on the “Relationships” view to see how your model looks like.

Note: As you can see I left the Date table in the model. I’ll express the reason in another post showing you  a very effective way to have all date measures in a single chart. So you can decide either to import all date tables into your model or implementing the report using just one date dimension depending on your client’s needs .

After importing all date tables to the model it’s time to manage the relationships.

• Click on the “Manage Relationships” button from the ribbon
• As you can see the new added date tables are not in the list at all

• Click New
• Select “Order Date” table from the first dropdown list
• Click on “OrderDateKey” column
• Select “Reseller Sales” table from the second dropdown list
• The “OrderDateKey” should get highlighted automatically. If it doesn’t click on it
• Click OK

• Do the same thing for the other date tables to create the relationships

Go to “Relationships” view to see how the model changed.

Data Visualisation

Now it’s time to start the exciting part of working with Power BI Desktop. Data visualisation is the very easy with Power BI Desktop when you have a well designed model. Let’s start with some scenarios and make meaningful reports.

Well, as you saw we used Adventure Works data warehouse as a sample in this post. We imported the tables related to “Reseller Sales”.  ***

Total Sales Amount

• Go to “Report” view
• Expand “Reseller Sales” table from “Fields” pane
• Click on “Sales Amount”

• Power BI Desktop creates a column chart by default. Select the chart
• From Visualisation pane click on “Card”

• Resize the card

Sales By Product Category

• Expand “Reseller Sales” from “Fields” pane
• Click “Sales Amount”
• Expand “Product Category”
• Click “Product Category” field
• To show the labels from the “Visualisation” pane click Format –> Data Labels–> On
• If you want to change the columns colours expand “Data Colours” and you can either change the colour for all columns or changing individual columns by clicking on “Show All” and change the colour of desired columns
• To change the chart title just expand “Title” and replace the “Text”

Sales and Total Costs by Region (Postal Code)

• Click somewhere on the report view out of any other charts
• Expand “Reseller Sales”
• Click “Sales Amount”
• Expand “Geography”
• Click “Postal Code”
• From “Visualisation” click “Map” to change the chart to a Map
• Drag and drop “Total Product Costs” from “Reseller Sales” into “Colour Saturation” area
• From “Visualisation” pane click on “Format”
• Expand “Data Colours”
• Click “Diverging”
• Change the colours for Medium, Centre and Maximum
• To change the chart name expand “Title” and change the “Text”
• To see more details you can zoom-in by double clicking on the map
• 
• If you hover over each circle it more details will be shown

Based on our settings this chart is telling us the sales amount and product costs in each postal code. The bigger the circle the higher sales amount. The greener circle indicates lesser product costs. So the red circle indicates the highest product costs.

Resellers with Sales Amount Greater Than $650,000 by Sales Region

• Add a Treemap to the report by clicking on it from the “Visualisation” pane

• Expand “Reseller Sales”
• Click “Sales Amount”
• Expand “Sales Territory”
• Drag and drop “Sales Territory Region” to “Group” into the “Visualisation” pane
• Expand “Reseller”
• Drag and drop “Reseller Name” into “Details”
• From “Visualisation” pane –> Filters –> expand “Sales Amount” and set “Show the items when the value: is greater that or equal to 650,000”
• Click “Apply Filter”

• To change the chart title click on “format”, expand “Title” then change the “Text”

• If you hover over the chart more details will be shown

Order Quantity, Unit Price and Sales Amount by Order Year and Product Category

• Click on “Scatter Chart” to add it to the report
• Expand “Reseller Sales”
• Drag and drop “Order Quantity” into X Axis area
• Drag and drop “Unit Price” into Y Axis area
• Expand “Product Category”
• Drag and drop “Product Category” into Legend area
• Expand “Order Date”
• Drag and drop “Year” into Details area
• Drag and drop “Sales Amount” from “Reseller Sales” into Size area

If you hover over the chart you can see some more details.

Adding Some Slicers

• Add a “Slicer” to the report by clicking on “Slicer” from the “Visualisation” pane
• Expand “Geography”
• Click “Country”
• Add another slicer for “State Province Name” as well

We will use these slicers to filter the whole report. So if we need to see the report just for analysing the data for the Unites States and Canada we just need to simply click on the on the slicer.

Interactive Filtering Ability

Power BI Desktop is a really amazing data visualisation tool with tons of features you can use. One of the great features od Power BI Desktop is “Interactive Filtering”. Interactive filtering basically is the ability of selecting values directly on a chart and having that filter on other data regions. It’s awesome isn’t it? So besides the slicers or the page filtering you can setup, you can easily click on a chart and see the effects all over the report. For instance, if you click on “Components” column from “Sales by Product Category” column chart you’ll that all other charts will be filtered very nicely.

Another example is when you want to see how your bike sales was in 2007. You can simply click on the desired circle on the scatter chart.

Publishing the Reports to Power BI Website

One of the fantastic new features added to the Power BI Desktop is the Publish button on the ribbon. Now we can simply publish our reports to the cloud directly from Power BI Desktop.

• Click on “Publish” button on the ribbon
• You just need to type your Power BI credentials and you’re good to go

You can simply click on the Open in Power BI link to open your Power BI report on the cloud.

There is a lot more to say about the awesomeness of Power BI Desktop. I will write some new posts regarding the other features soon.