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.

Calculated Element Transform
Calculated Element Transform

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

Adding calculated element
Adding calculated element

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

Edit expression for boolean
Edit expression for boolean

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;
}

Script Editor with the expression
Script Editor with the expression

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.

Available placeholders
Available placeholders

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.

Result of calculated element
Result of calculated element

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.

Edit Calculated Element column in Filter
Edit Calculated Element column in Filter

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

True value
True value

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

Output elements
Output elements

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