[]
        
(Showing Draft Content)

Syntax

This article describes the basic structure of analytical expressions in Wyn Enterprise.


Basic Syntax for Analytical Expressions

Name Requirements

Typically, a data model is composed of multiple data tables and data columns in a database that is stored in a database server. All the tables in that database must have a unique name and also all the columns in a particular table should also have a unique name.

The name of the entities in a data model is not case-sensitive. Example: FACTONLINESALES and FactOnlineSales will represent the same table.

Calculated columns and measures added to an existing data model must also belong to a specific table. The original column/calculated column/measure name in the table must also be unique.

Add Tables and Columns Reference

When creating analytical expressions, you add a reference to the tables and columns on which you want to perform the calculations. The basic format to add a column reference in an expression is to enclose the table name within single quotes, followed by the name of the column in square brackets. For example,

'FactOnlineSales'[SalesAmount]

In some cases, like, using the table manipulation function to create a new column, you have to use the "unqualified" format which is the column name in square brackets. For example, when you are referencing a scalar value from the same row of the current table, you can use the unqualified column name format [SalesAmount].

Tables

  • As already mentioned above, table names should be unique in a database. So whenever a column used, is from a different table than the current table, then the table name is required.

  • Table names must be enclosed within single quotes if they contain spaces, any non-English alphanumeric characters, or other special characters.

Calculated Columns / Measures

  • Names must always be in brackets.

  • Names can contain spaces.

  • Each name must be unique within an entity.

Listed below in the table are examples of some object names

Object

Object Name

Comment

Table Name

RetailDataset

If there is no space or any special character in the table name, then the name does not need to be enclosed in single quotation marks.

Table Name

' Retail Dataset'

If there are spaces, tabs, or other special characters in the table name, then enclose the name in single quotation marks.

Fully qualified column name

RetailDataset[Quantity]

The table name precedes the column name, and the column name is enclosed in square brackets.

Unqualified column name

[Quantity]

The unqualified name is just the column name, in square brackets. Contexts where you can use the unqualified name include formulas in a calculated column within the same table, or in an aggregation function that is scanning over the same table.

Fully qualified column in a table with spaces

'Retail Dataset'[Quantity]

The table name contains spaces, so it must be enclosed in a single quotation mark.

Other restrictions

The syntax applicable for each function and the kind of operations it can perform varies a lot. However, in general, rules applicable to all the functions and expressions are listed below:

  • Formulas and expressions in WAX cannot insert or modify individual values in the tables.

  • By using WAX, you can create only calculated columns and measures. Calculated rows are not created.

  • You can nest functions any number of times while defining calculated columns.

  • WAX has many functions that return a table. Normally, you use the values returned by these functions as input to other functions, which require a table as input.

Data types

In WAX, there is no need to convert, cast, or otherwise specify the data type of a column or value that you use in a formula. WAX automatically identifies the data types for the following:

  • Data types in the referenced columns

  • Data types of the values that you type in

For these data types, WAX performs implicit conversions where ever required to complete the specified operation.


Listed below are some of the limitations to the values that can be converted:

  • WAX will return an error in case a column or a value has a data type that is not compatible with the current operation.

  • In WAX there are no functions that let you explicitly convert, change, or cast the data type of existing data that you have imported into a data model.

Define Functions

Analytical expressions support a varied range of inbuilt functions. These functions can be used for performing complex calculations such as aggregation functions, date and time functions, filter functions, and so on. A function consists of a name and argument(s). The name of the function must be followed by a parenthesis in which the parameters are passed.

SUMX(ALL('FactOnlineSales'), 'FactOnlineSales'[SalesAmount])

Use Parameters

Wyn Enterprise supports parameters in WAX expressions. This gives users the flexibility to change the output results based on the parameter values. When creating an expression, click Parameters to add a dashboard parameter.

Note: Only single-valued parameters can be used in WAX expressions.

To include user-related information in the WAX expressions, bind parameters to user-context or organization context values.


Add Parameters to WAX expression


Specifying Parameters


Following is the list of parameters supported in WAX.

Parameter

Description

Expression

Any WAX expression that returns a single (scalar) value. The expression is evaluated multiple times for each row or context.

Value

Any WAX expression that returns a single (scalar) value. The expression is evaluated exactly once before all the other operations.

Table

Any WAX expression that returns the data of the table. It could be a table or a table manipulation function.

TableName

Returns the name of the table. It cannot be an expression.

ColumnName

Returns the name of the column. It cannot be an expression.

Name

A string constant that provides the name of the new object.

Order

An enumeration that determines the sort order.