Creating Maintainable Power BI Report Models – Part 2

In this installment, the user facing aspects of your Power BI report models will be discussed. Much of this is considered fit and finish work. It is important that you take the time to address these issues as PowerBI.com enables users to build their own reports and dashboards from your data sets. This finish work will result in less time spent on support and making ad hoc updates.

Hiding data sets and fields in the Power BI Report View

When you are creating a complex report model, you may use transitional data sets or configuration type data sets to create your final data set. These transitional or configuration data is not intended to be used by the end user in creating a dashboard or report. Before releasing a report model, you should determine which of these data sets and fields to hide from the Report view. Doing so will reduce user confusion about this information, allowing them to focus on the data most important to them.

To hide a data set or field in the report view, do the following.

• In Power BI Desktop, click on the Datasheet icon (1)

• In the Field well, right click on a data set name or on a field
• Select Hide in Report View (2)

• The data set or field will appear grayed out (3)

Default formatting

Default formatting has a cumulative positive productivity effect for your users. Setting the default format for numbers and dates frees them from having to spend non-productive time “trying to get the data to look right.” It’s tedious work that can easily be eliminated with a few actions on your part. Your users will be happier and able to be faster at generating views.

Dates

In many data sources, dates are stored as datetime values. Project Online stores every date in this format and this makes sense from a transactional perspective. However, in the BI world, most users aren’t looking for time information. The time data creates visual noise instead.

Also, if you are creating content for international users, it is important to use the right formats such that Power BI uses the end user’s regional setting. This way your American and Australian counter parts both know that 3/11/2016 and 11/3/2016 are respectively, the same date. Date and Datetime formats that automatically adjust to the end user’s regional settings are marked with an asterisk *.

To set the default format for a date, do the following.

• Power BI Desktop, click on the Datasheet icon (1)

• In the grid, click the heading of the column to format. Only one column can be selected at a time (2)

• Click the Modeling tab in the ribbon (3)

• Change Data Type to Date. (4) Note, the format of the selected date column has changed.

• Click Format dropdown, Date Time, select the first value, which has an asterisk (5)

Now, your date values will appear as follows.

Numbers

The same process applies to numbers as well. Many systems store numbers in a general decimal number format, leaving users staring at numbers with a variable number of decimals or cost data that isn’t apparent it’s related to money. Variable decimal places creates usability problems as it’s hard to scan the data. Not denoting costs with a currency symbol leaves the number open to misinterpretation.

Decimal Numbers

To format the decimal positions in a number, do the following.

• In Power BI Desktop, click on the Datasheet icon (1)

• In the grid, click the heading of the column to format. Only one column can be selected at a time (2)

• Click the Modeling tab in the ribbon (3)

• Change Format to Decimal Number (4)

• Set the number of decimals to show (5)

Costs

To format a number with a currency symbol, do the following.

• In Power BI Desktop, click on the Datasheet icon (1)

• In the grid, click the heading of the column to format. Only one column can be selected at a time (2)

• Click the Modeling tab in the ribbon (3)

• Change Format to Fixed Decimal Number (4)

• Set the number of decimals to show (5)

Categorization

The last set of good practices for report models is to categorize specific types of data such that the default behavior in Power BI is more user friendly. This applies currently to location based data and urls. By default, Power BI would treat this information as character data. If a user drags a city or country value to a dashboard, likely they will be interested in a map view rather than the name of the city or country. Categorization allows you to tell Power BI that the City or Country field is location data and that a map instead of a grid should be rendered by default. For URLs,, categorization tells Power BI whether to render the link as clickable or to render the image to which it points.

In all cases, click the column heading of the field to categorize and select the Data Category on the Modeling tab to set, similar to the actions above.

Location Data

You can categorize text data as location related or numeric data if it contains latitude or longitude values. The list of location categories is shown below.

Web URLs

If you want to make the URL clickable, change the Data Category to Web URL.

Image URLs

To render the image accessed by the URL, change the Data Category to Image URL

Following these simple techniques will result in easier to use and easier to maintain report models.