Wyn analytical expression is a formula expression language that is used to perform advanced calculations in direct query models, cached models, cached datasets, and dashboards. These expressions consist of functions, operators, and value references, which are evaluated as a formula to generate results. In simpler words, analytical expressions help you create new data from the existing data in the data models and datasets.

Usually, the data you want to visualize is readily available in the datasets and data models (as table columns) themselves. However, there can be scenarios where you might need to perform certain calculations on the data for deeper insights.

For example, have a look at the below table in which a new calculated column is added to show the COGS (Cost of Goods Sales) for each product. The calculated column (COGS) uses the following analytical expression -

COGS = 'FactSales'[SalesAmount] * 'FactSales'[UnitPrice]

So, WAX can help you create new data from data already in your model.

Note: You can use the AutoComplete of the expression editor for correct syntax, functions, and formatting errors. It does not help with the correctness of the expression logic.

Expressions ultimately rely on relationships in the data model when performing data operations. Only expressions that conform to logical relationships ultimately return correct results.

Common Concepts in WAX

Listed below is an introduction to the common concepts of Wyn analytical expressions.

Calculated Column

A model calculation for adding fields to the target table using analytical expressions. The expression must return a scalar value and is calculated for each row in the table. A calculated column is often used in arithmetic operations or string processing.

Measure

A model calculation for adding fields to the target table using analytical expressions, which calculates aggregation operations on multiple rows of the table according to the context. The measure is often used to calculate total, count, average, percentage, etc. according to the different dimensions.

Calculated Table

A model calculation for generating a table by writing an analytical expression. It could be an original table(such as 'Sales') or a table function that returns a table(such as filter('Sales','Sales'[Quantity] > 1)). Usually, It appears in the first parameter of aggregation or window functions. Currently, the Wyn dashboard automatically generates a complete calculation table based on the user input for querying data.

Context

It describes the environment in which an analytical expression is evaluated. It consists of dimension context and filter context.

• Dimension Context: The dimension context can be understood as sending the dimension to the analytical expression.

  Example: In this scenario, we will create a calculated column (dimension) to classify the sales manager's performance based on the sales revenue generated.

  So, we will create a calculated column Performance based and write the following expression:

SWITCH (
    TRUE,
    'SalesRevenue'[Revenue Generated] < 11000, "Low Performance",
    'SalesRevenue'[Revenue Generated] >= 11000 && 'SalesRevenue'[Revenue Generated] <= 20000, "Medium Performance",
    'SalesRevenue'[Revenue Generated] > 20000, "High Performance"
)

The dashboard created is as shown below:

• Filter Context: The filter context can be understood as filtering the data of the original table of analytical expression.

  By default, the calculated column looks at the whole data set. In order to display the filtered data, you can add a filter condition.

  Example: Let us here display the sales manager's performance based on the sales revenue of 2020. So, we will add a filter in our dashboard.

Now the classification of sales managers is changed based on the Sales Revenue for the year 2020 as shown below.

Expression

It is a unit of analytical expression logic that evaluates and returns a result. Expression is constructed by using model objects (tables, columns, or measures), functions, operators, or constants.

Name

As an alias, it's a text name that users can customize. It is used to set the expression's name.

Function

It contains one or more arguments that allow passing input parameters. The name of the function must be followed by a parenthesis in which the parameters are passed. A function can include multiple function calls as well as nested functions.

For more details refer to Functions

• Nested Functions: You can use multiple functions in a formula. In this scenario, the result from one function is used as an argument of another function. Nested functions return a table and these tables cannot be saved directly as a result. It must be provided as an input to some table function. For example, the functions MINX, AVERAGEX, and SUMX need a table as the first argument.

Queries

WAX queries can be created and run in Wyn. A WAX query is a statement, and it is similar to a SELECT statement in SQL. The most basic type of WAX query is an evaluate statement. For example:

EVALUATE
FILTER('Sales','Sales'[Quantity] > 1)
ORDER BY  'Sales'[Amount] DESC

This will return a table, listing only those Sales with a quantity greater than 1, in descending order by Amount. You can also create measures or calculated columns as part of the query.

Note: The whole WAX query in Wyn is generated automatically. The users couldn't write a whole WAX query(such as above). In Wyn, the users could only create calculated columns/ measures, and drag the original column /calculated column/ measures to the values or axis, or use them as filter or order, In one word, the user could only create the custom elements and drag any elements into the chart, our system does the combination work.

For example, here we have created a calculated column named SaleAmount as below:

Drag original dimension Year to Axis and drag SaleAmount to Values. The result is as shown below:

The Wyn does the combination work for Year and SaleAmount, generates the whole WAX query to the backend, executes, and returns the result. The whole WAX for this case is as below:

EVALUATE
SUMMARIZECOLUMNS(
     'RetailDataset'[Year],
     "RetailDataset'_'RetailDataset'[SaleAmount]_SUMX",
      SUMX('RetailDataset','RetailDataset'[Unit price]*'RetailDataset'[Quantity])
)

Formulas

WAX formulas are important for calculations in calculated columns and measures. This section will help you with understanding the basics of WAX formulas.

WAX formulas can be simple or complex. Listed below in the table are some of the examples of simple formulas that you can use in a calculated column.

For adding calculated columns and measures, perform the following steps:

• Click the target table setting and choose Add Calculated Column or Add Measure.

  
  

  
  

• You can either write or select a function name, or write an expression.

• Start typing the first few letters of the function or name, the AutoComplete will display a list of available functions, tables, and columns.

• Provide arguments to the functions. For this, you need to either select the arguments from the dropdown list containing the possible tables and columns, or you can simply type in the values.

  
  

  
  

• Wyn will automatically check for syntax errors and ensures that all parentheses are closed and columns, tables, and values are referenced correctly.

• Press OK to accept the formula.

In this example below, let us look at a formula in measure named Total in Current Year:

This measure is used to create an aggregation result for the current year. This formula contains the following elements:

AutoComplete

With the help of AutoComplete, you can enter a valid formula syntax as it provides the options for each element in the formula.

• In a formula with nested functions, you can use the formula AutoComplete in the middle of the formula. The value of the text at the insertion point is displayed in a dropdown list and the text after the insertion point is not changed.

• While writing functions, you need to make sure that the syntax is correct else AutoComplete will give an error and you will not be able to save and use it. Example: AutoComplete will not add or match the parenthesis automatically, in case the closing parenthesis is missing.

Variables

You can create a variable using the keyword VAR within an expression. These variables can store the result of an expression and can then be passed as an argument to other measures expressions. Example:

VAR
    TotalQuantity = SUM('RetailDataset'[Quantity])

Return

    IF (
        TotalQuantity > 1000,
        TotalQuantity * 1.10
        )

In this example, TotalQuantity is the name of the Variable. You can pass TotalQuantity as a variable to other expressions.