Handling missing data

Contents[Hide]

1. Overview

This article shows you how you can handle null values and missing data in your metric set. If rows are missing from your data or have measure values that are null or missing, you can set up a missing data rule to display them and optionally fill in their measure values.

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

2. Example data

2.1. Missing measure values

This first example uses the Sales.SalesPerson table from the AdventureWorks sample database, which contains rows where the SalesQuota column is null (missing).

First, drop the BusinessEntityID column onto a metric set or dashboard:

Table listing all BusinessEntityIDs
Table listing all BusinessEntityIDs

Then, drop SalesQuota onto the area over the table visualization labeled Column:

BusinessEntityIDs with SalesQuota values
BusinessEntityIDs with SalesQuota values

Notice that BusinessEntityID 274 is now missing in the list after the SalesQuota was added. This is because there is no SalesQuota value in that row, but you can display it using a missing data rule.

2.2. Missing dates or hierarchy values

As another example, the following data source is missing entire rows corresponding with June 3rd & 4th, and contains rows only for the 1st, 2nd, and 5th:

Missing dates
Missing dates

This column of dates was promoted to a time dimension hierarchy in a data cube ahead of time, and its Day level was dragged onto the canvas to create a metric set. When working with a column of dates directly from a data connector, you can expand it to drag a time dimension hierarchy level without leaving your metric set or dashboard.

A time dimension is able to add in entire rows of data for dates that are missing from your data source according to the current level of the hierarchy, such as Day, Month, Hour, etc. Similarly, a predefined hierarchy can fill in entire rows missing for other types of values.

Note
If you do not use a time dimension or predefined hierarchy, missing data rules can only fill in measure values that are missing in existing rows where other values are already present in the data source, like for the BusinessEntityID example earlier.

After adding a measure to the Data Analysis Panel, the data is automatically visualized as a line chart showing gaps for the missing dates. In this example, markers were also enabled to clearly show each data point along the line:

Line chart with missing dates
Line chart with missing dates

Tip
If you have a chart like the above and only want to fill in the missing axis labels, you can also remove the time dimension from under Bottom Axis Label in the Data Analysis Panel's Visualization tab and allow the chart to label the axis on its own.

3. Using a missing data rule

To include the missing rows or measure values, click to edit the measure in the Data Analysis Panel.

Edit the measure
Edit the measure

In the dialog under Element Settings, set the Missing Data Input Rule to determine which missing data to include:

Missing data input rule
Missing data input rule

  • Ignore Nulls (default) – does not include any measure values that were null or missing.
  • Source Nulls – includes only the null measure values present in the records in the data source.
  • Full Nulls – includes all possible null values even if records did not exist for them in the data source.

Note
If you have multiple hierarchies, the Full Nulls option includes all possible combinations of values and may consume a lot of memory for large data sources.

If we choose Source Nulls for our first example, BusinessEntityID 274 can be displayed. When using a time dimension or predefined hierarchy like in the second example, the Full Nulls option can fill in missing hierarchy values.

Next, set the Missing Data Output Rule to determine how missing measure values should be displayed:

Missing data output rule
Missing data output rule

  • None (default) – does not display data where the measure value was null or missing, and excludes those rows or columns.
  • 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.
    Averaging between each point
    Averaging between each point
  • Average Non-Missing Data – displays the average between the previous and next data points that were not missing measure values.
    Average of non-missing data
    Average of non-missing data
  • Rolling Average – displays the average of a certain number of previous data points determined by the Rolling Average Period setting.
    Rolling average of previous two
    Rolling average of previous two

For the options that calculate an average, also set Axis The Average Will Affect. This is typically Rows to use next and previous rows in a table or data points in a series, but can be Columns.

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 first example, BusinessEntityID 274 can now show N/A for SalesQuota.

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

4. Styling missing data

You can style cells or data points differently for missing data using the Properties window.

4.1. Table

In the Look tab of the properties for a table, select Missing Data Style.

Select Missing Data Style
Select Missing Data Style

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

4.2. Chart

In a chart, go to the properties for its data point series. You can right-click a chart's data points when editing and choose Series Properties.

Go to the Look tab to find Missing Data Style, click + to add one if there is none, then click to navigate to the style settings.

Data point missing data style
Data point missing data style

In this example, data points that were filled in using a missing data rule are distinguished using a thin black line stroke and hollow markers:

Styled missing data points
Styled missing data points

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:
Phone: 9am-6pm, ET, Mon-Fri
Email: 7am-6pm, ET, Mon-Fri