Highlight a cell in a table based on a measure in a different column
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.
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.
From the toolbar, change the structure of the table to display it as a flat table.
2.2. Hide the column
From the Properties panel, select the DaysToManufacture column and uncheck the visible property to hide the 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.
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.
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.
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.
Edit the Appearance properties of the state style according to the requirement.
The resulting table will look like this:
3. See Also