# Filter data cube using complex logical expression

Contents[Hide]

## 1. Overview

This article shows you how to filter the data cube with complex logical expressions.

In cases of logical expressions that use a combination of AND and OR operators, the Filter transform is not adequate to filter the data needed and we need to combine it with a Calculated Element transform.

## 2. Create the Calculated Element transform

For this example create a data cube with the [Sales].[SalesPerson] table from the Adventure Works database.

Once the table has been placed in the Data Cube Designer, place the Calculated Element transform.

Configure the transform and click the Add icon under Calculated elements.

Name the calculated element, set the data type of the element to Boolean, and click Edit expression in editor to open the Script Editor.

In the Script Editor, place the conditions that you want to filter out of the data cube. Make sure that it meets the criteria you want to accomplish, and return "True."

For example, we want to return values that have the following condition: (COL_A > 5 AND COL_B > 5000) OR (COL_A = 1 AND COL_C > 0.01).

In this case, we will use the following script:

```if ((\$TerritoryID\$ > 5 && \$SalesYTD\$ > 5000) || (\$TerritoryID\$ == 1 && \$CommissionPct\$ > 0.01)){
return true;
}```

For the list of columns that you could use to create the expression, you could check on the Placeholders section in the Calculated Element screen.

Click Save, and it will refresh the data cube to show the results. It will have another column in the data cube to display the results of the transform.

Take note of the statistics shown in the Data Preview panel. The number in brackets indicates the count of distinct records (in this case, 17). Click this value to view the statistics per column in the data.

## 3. Create the Filter transform

Add a Filter transform after the calculated element and configure it.

In the Filter dialog, click Edit next to the created Calculated Element.

Select the Element Operator Equals and place a checkmark in the True checkbox.

Click OK and select the Process Results.

Click the minus (-) icon next to the created calculated element to hide it from the output.

In the Data Preview, you will see the filtered results. Note that the shown statistics have been updated as well (in this example, showing only 8 records).