Highlight a cell in a table based on a measure in a different column

Contents[Hide]

1. Overview

Often a table needs to visually tell a story, without cluttering the view with implied or predefined information. For example, a manager might want to know which products are bringing him the most profits/loss, without reading through the entire data in the table.

This article walks through the steps required to highlight a cell value in a flat table based on a measure in a different column using states. 

2. Highlight the cell

For this example, instead of having DaysToManufacture represented as a separate column in the table, we are going to visually represent DaysToManufacture using states, such that the product with the number of manufacturing days greater than 0 will be highlighted.

Table with <em>DaysToManufacture</em> column
Table with DaysToManufacture column

2.1. Create Table

Using Adventure Works database, drag and drop two measures ListPrice and DaysToManufacture on the dashboard canvas. Drag and drop the Name dimension onto the Rows field of the Data Binding Panel.

Table with two measures and a dimension
Table with two measures and a dimension

From the toolbar, change the structure of the table to display it as a flat table.

Display as flat table
Display as flat table

2.2. Hide the column

From the Properties panel, select the DaysToManufacture column and uncheck the visible property to hide the column.

Hide the DaysToManufacture column
Hide the DaysToManufacture column

 Now, only the ListPrice measure and the Name dimension are visible on the table.

2.3. Set up States

From the toolbar, select Data Tools and select Set Up States.

Select Set Up States from Data Tools
Select Set Up States from Data Tools

2.3.1. Add a State Group

From the Set Up States dialog, click Add a State Group.

This opens the Configure State Group dialog.

Click the measure dropdown and choose the measure DaysToManufacture.

Give the state group a name and enter an optional description.

Configure state group dialog
Configure state group dialog

2.3.2. Add state to the State Group

In the Set Up States dialog, click Add in order to add the state to the State Group which checks if the product took longer than a day to manufacture.

The Configure State dialog is displayed.

Enter a name for the state and an optional description.

Click Add Condition. A greater than expression involving the DaysToManufacture measure is automatically added.

Click the Greater Than button and select Greater Than or Equal To from the dropdown menu.

Click Select a Target and choose Constant(Formula). Add 1 to the textbox.

Add a state to the state group
Add a state to the state group

2.4. Apply state to Column

In the example, the state is applied to the Name Column.

From the Properties panel, select the Name column and select Look Tab.

Scroll to the State Styles section and select Add. Select the newly created state style and configure the style.

Apply state to the Name column
Apply state to the Name column

Edit the Appearance properties of the state style according to the requirement.

Change the appearance of the state
Change the appearance of the state

The resulting table will look like this:

Table with highlighted cells representing <em>DaysToManufacture</em>
Table with highlighted cells representing DaysToManufacture

3. 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