(v23) Filter data cube using complex logical expression

Filter data cube using complex logical expression

This applies to: Managed Dashboards, Managed Reports

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.

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.

Configure the transform and click to add a calculated element.

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

Enter an expression usingDundasScript 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.

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

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.

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.