Filtering Data With a Parameter

Answer

Parameters cannot be used directly in the view, and instead need to be added to a dimension filter or a calculated field.

Option 1: Add parameter to dimension filter

A numeric parameter can be used in the Top tab of a dimension filter. See Using a Top N Parameter to Filter a Table for directions.

Option 2: Create calculated field referencing parameter

The example workbook, available for download in the right-hand pane of this article, uses the sample data set Superstore to demonstrate the filtering a bar chart of sales by state to a region selected in a parameter.

Step 1: Creating the parameter

Right-click in the data pane and select Create Parameter....
In the dialog box, name the parameter. In this example, the parameter is named "Region Parameter".
Under Data type select String.
Under Allowable values select List.
Click Add from Field > Region.
Add an additional value of All with the display text of All.
Click OK.

Step 2: Creating the calculated field

Select Analysis > Create Calculated Field
In the Calculated Field dialog box that opens, do the following, and then click OK:
1. Name the calculated field. In this example, the calculated field is named "Region Parameter Filter"
2. In the formula field, create a calculation similar to the following:
```
[Region Parameter] = [Region]
OR [Region Parameter] = "All"
```

Step 3: Creating the view

Drag [Sales] to the columns shelf
Drag [State/Province] to the Rows shelf
Right-click on [Region Parameter] in the data pane and select Show Parameter Control
Drag [Region Parameter Filter] to the Filters shelf
In the Filter dialog, check True and click OK

Additional Examples

Parameters can be strings, like above, or can be numeric, Boolean, or date values. The following example calculations cover these different data types. All examples are demonstrated in the attached example workbook.

Numeric Parameter

SUM( [Sales] ) >= [Sales Parameter]

Boolean Parameter

SUM( [Sales] ) >= 100000
OR NOT [Show Only High Sales Parameter]

This calculation will return True when SUM([Sales]) is above 100,000 or if False is selected in [Show Only High Sales Parameter]. The operator NOT converts False into True, and when a value is selected in a parameter then that value applies for every record in the data set.

Date parameter

[Order Date] >= [Start Date Parameter]
AND [Order Date] <= [End Date Parameter]