Build your First Report in Microsoft Power BI Designer Part 2, Make it More User Friendly

In this post I would like to explain more details about Power BI Designer features. In the previous post you learnt how to create some very simple reports. However, those reports were just for testing general features of the tool. For instance we didn’t even play with very simple features like renaming the dimension or fact tables and members to user friendly names. In this article not only do I explain some of the simple ones, but also I’ll go through some of the more advanced ones.

Again, as per my previous post, I’m using AdventureWorksDW2012 as a source database.  We imported “Internet Sales” into the designer and we created some reports and one new page and we saved the reports on disc. So we have all the requirements on hand. Let’s go…

Making names more user friendly:

• Open the *.pbix report file in Power BI Designer
• Double click on “DimCurrency” to rename it to “Currency”. We need to do the same for all other objects

• We also need to do the same for the fields which are getting used on the reports

• You can do the same in “Query” section as well. To do so, click on “Query”  then click on the queries on the right pane . Then  on the “Query Settings” pane you can rename the query (Power Query uses the name “Query” for all imported objects which could be tables, views etc.).

Hiding Unused Fields:

There are always some fields that shouldn’t be hidden from the end user sight. For instance, all ID fields should be hidden. You can do very easily from the “Report” section.

• From the “Report” section, right click on the field then click “Hide”
• You can also click on the drop down arrow on the right of each field and do the same

• After hiding a field it disappears from the fields list. To see the hidden fields right click on the field and tick “View Hidden”

• To unhide a field, just right click on it and un-tick “Hide”

• You can also unhide all hidden fields

Managing relationships:

We are going to take a step further and have a look how we can manage the relationships between the queries. Click on “Manage” from “Relationships” section on the ribbon.

As you can see Power BI Designer automatically detected some relationships. Looking at the relationships more precisely, you’ll see that there are some missing relationships. For instance, the relationship between “Internet Sales” and “Internet Sales Reason” is missed. This is because composite relationship is not supported in Power BI Designer. As the same in memory technology as Tabular Models and PowerPivot is used it’s not a surprise that composite keys are not supported in the tool. I have a post regarding composite keys in tabular model that you might want to have a look.

Now we want to create a new relationship between “Internet Sales” and “Internet Sales Reason” queries. We’ll go through the same procedure as I explained on composite keys in tabular model.

We want to concatenate both key columns from both “Internet Sales” and “Internet Sales Reason” queries to make a new key column.

• From “Query” select “Internet Sales Reason”
• Right click on “SalesOrderNumber” column-> Change type-> Text

• Click “Add Custom Column” from “Add Column” tab from the ribbon
• Name the Column as “ID”
• Concatenate “SalesOrderNumber” and “SalesOrderLineNumber” columns using “&” operator then click OK.

• You should see the new “ID” column in the “Internet Sales Reason”

• Now add a new “ID” column to “Internet Sales” just like above

• Go to “Report” section and click “Manage” from “Relationships” section on the ribbon
• Click “New”
• From the first drop down list select “Internet Sales”
• Scroll to the end tight of the table and select “ID” column
• From the second drop down list select “internet Sales Reason”
• The “ID” column should be highlighted. If it’s not, just select it.
• Click OK

• You should see the new relationship in the list

Expand Record Column:

If you go to “Query” section and click on “Internet Sales Reason”, remember that we renamed it before to make it more user friendly, you’ll see two additional columns added to the query that do not exist in the original tables. The designer intelligently added the related columns from other related tables. Note that we didn’t import “DimSalesReason” table into the model, but the designer detected the relationship and added the related column to the query fields. This is just like what happens in Power Query when you’re going through the same process.

In this sample, we don’t want to import “DimSalesReason” table into the model. So we’re going to use an awesome feature which is called “ExpandRecordColumn”. Using this feature we’ll expand the “DimSalesReason” column so we’ll have some selected columns of “DimSalesReason” side-by-side the “Internet Sales Reason” columns.

To do so, just simply click on the expand button to the to the “DimSalesReason” column,  tick “SalesReasonName” and “SalesReasonReasonType” the click OK.

As you can the two selected columns are added to the query.

Now rename the columns to user friendly names. To do so, just simply double click on the column name and rename it.

Create a New Reports Using the New Relationship Created:

Navigate to page 2 of the report and add the following reports to the page. You should be able to create the reports now :

1. Internet Sales Amount by Sales Reason Type
2. Internet Sales Amount by Sales Reason

Now click on promotion bar on the “Internet Sales Amount by Sales Reason Type” visualisation and as you’ll see it affects the other visualisasion as well.

In the next article I’ll explain how to make fantastic dashboards using the report we created so far.

Enjoy.