WHERE Clause

The WHERE Clause, located at the bottom of the Data Model Designer, lets you filter the data returned by your model using SQL conditions. By specifying conditions, you can limit result sets to only the rows that meet your criteria. This is often used to improve query performance or narrow the dataset to relevant values.

When to use the where clause

• Filtering datasets for analysis: Include only relevant records.
• Improving query performance: Limit dataset size to speed up processing.

Build a WHERE clause

1. In Data Models and Views, right-click a data model and select Data Model Designer.
2. In the WHERE Clause pane, click inside the text box.
3. Type the SQL expression that defines your filter condition.
4. Click the three vertical dots on the right to open the SQL Scripts Builder.
5. Use available keywords, tables, operators, or code helpers to complete your logic.
6. Click Validate to check if the expression syntax is correct.
7. Click Save.
8. On the File tab, click Save Data Model.

WHERE clause fields

Field	Description
Script editor	Enter the SQL condition used to filter data in the cube.
Code helpers	Insert operators, brackets, or keywords using toolbar buttons above the script editor.
Scripts Keywords	Build expressions using grouped keywords: Functions – Perform standard calculations or data transformations using math or text functions. BI Functions – Apply business intelligence logic, such as period-over-period comparisons or trend analysis. Statements – Control the logic and structure of your expression such as CASE for conditional rules. Operators – Add, compare, assign, or combine values using logical or arithmetic symbols. Constants – Insert fixed values that do not change, such as numerical thresholds or static text. Objects – Use advanced elements such as Hyperlink or special script functions to enhance interactivity or dynamic output.
Tables	Select from tables available in the data source to reference in the script.
Others	Add additional elements: Global Variables – Use predefined global values. Special Variables – Use context-sensitive or system-generated values. Data Model Parameters – Apply parameters defined in the model to make scripts dynamic.
Validate	Check whether the script executes successfully and meets syntax requirements.

Examples

Example 1 – Filter by date range:

WHERE OrderDate BETWEEN '2023-01-01' AND '2023-12-31'

Example 2 – Filter by company code:

WHERE CompanyCode = 'COMP001'

Example 3 – Filter by multiple regions:

WHERE Region IN ('North America', 'Europe', 'Asia Pacific')