Filter data cube using complex logical expression


1. Overview 

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

For many filter operations, the Filter transform can be used, which allows you to use either an And or Or operator between multiple conditions. If you need to combine both And and Or operators, you may need to use an expression such as the ones supported by the Calculated Element transform.

2. Create the Calculated Element transform

In this example, we have dragged the [Sales].[SalesPerson] table from the Adventure Works database onto a new data cube.

Add the Calculated Element transform from Insert Common in the toolbar.

Calculated Element transform
Calculated Element transform

Configure the transform and click to add a calculated element.

Adding calculated element
Adding calculated element

Set the Name and Data Type of the element. Our expression will return a data type of Boolean.

Enter an expression (using DundasScript) that includes your desired combination of filter conditions. Our example returns true for rows where (COL_A > 5 AND COL_B > 5000) OR (COL_A = 1 AND COL_C > 0.01).

In our case, we will use the following script:

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

Type a dollar sign ($) for a popup to suggest the placeholders that are available to refer to columns in your data, or click to expand the placeholders section for a list.

Available placeholders
Available placeholders

After submitting your changes, expand the Data Preview to see the results. They should include your new column.

Result of calculated element
Result of calculated element

3. Create the Filter transform

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

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

Filter by the calculated element values
Filter by the calculated element values

Set Element Operator to Equals and select the True checkbox to require that the expression return true.

Require that the expression returns true
Require that the expression returns true

Submit the Transform Element dialog and uncheck the checkbox next to this calculated element to remove it from the filter transform's output (but still use it for filtering).

Submit the dialog and return to the Data Preview window to see the filtered results.

Filtered result
Filtered result

4. See also

Dundas Data Visualization, Inc.
400-15 Gervais Drive
Toronto, ON, Canada
M3C 1Y8

North America: 1.800.463.1492
International: 1.416.467.5100

Dundas Support Hours:
Phone: 9am-6pm, ET, Mon-Fri
Email: 7am-6pm, ET, Mon-Fri