Handling null data points

Contents[Hide]

1. Overview

This article shows you how null values and missing data can be set up to be displayed. If there are rows that have no data, or with measure values that are null or missing, you can set up a rule to display those rows and optionally insert replacement measure values.

By default, measure values that are null or missing are not displayed by the metric set or its visualization.

2. Example data

In this example, we will use the Sales.SalesPerson table from the AdventureWorks database. First, drop the SalesPerson.BusinessEntityID on the dashboard canvas to show the rows:

Table with Business Entity ID
Table with Business Entity ID

Then, drop SalesQuota in the Column area:

Adding SalesQuota in the Table
Adding SalesQuota in the Table

Notice that BusinessEntityID 274 is missing in the list after the SalesQuota was dropped in the table.

3. Using a missing data rule

To view the missing BusinessEntityID, open the Data Analysis Panel, and edit the measure (in this case, SalesQuota).

Edit Measure
Edit Measure

In the dialog, under Metric Set Default Values, go to Missing Data Input Rule. This setting determines which values should be considered missing and included.

Missing Data Input Rule
Missing Data Input Rule

You can choose from these options:

  • Ignore Nulls (default) – does not include any null values. 
  • Source Nulls – includes only the null values present in the records in the data source.
  • Full Nulls – includes all null values. This means all possible hierarchy member combinations will be displayed, even if individual records did not exist for them with null values in the data source.

Note
Working with Full Nulls may consume a lot of memory when working with multiple hierarchies.

If we choose Source Nulls in our example, BusinessEntityID 274 can be displayed.

Go to Missing Data Output Rule. In this area, you will find options for how to display the missing data points.

Missing Data Output Rule
Missing Data Output Rule

You can choose from these options:

  • None – does not display data where the measure value was null or missing.
  • Empty – a row, column, or data point is included but the measure value is left null/blank.
  • Zero – inserts zeros where the measure value was missing.
  • Average – displays the average between the previous and next data points.
  • Average Non-Missing Data – displays the average between the previous and next data points that were not missing measure values.
  • Rolling Average – displays the average of the previous data points.

If you choose Empty, the Empty Value Text field will allow you to optionally insert text to be displayed in place of the measure value (e.g., N/A).

Setting Empty Value Text
Setting Empty Value Text

In our example, BusinessEntityID 274 now shows N/A for SalesQuota.

Table with N/A values
Table with N/A values

4. Styling missing data cells

You can style cells or data points with missing data to look different than the actual data source values.

When using a table visualization, open the Properties window for the table.

In the Look tab, select Missing Data Style.

Select Missing Data Style
Select Missing Data Style

For example, the Cell Color and Font Color properties can be set to distinguish cells showing data calculated by the missing data rule.

Set the cell and font colors
Set the cell and font colors

Similarly, you can set the Missing Data Style property of a chart series to make data points appear different when calculated by a missing data rule.

5. See also

 

Dundas Data Visualization, Inc.
500-250 Ferrand Drive
Toronto, ON, Canada
M3C 3G8

North America: 1.800.463.1492
International: 1.416.467.5100

Dundas Support Hours: 7am-6pm, ET, Mon-Fri