## Description

**Works with BY modifier to point to a mapping metric to transform one or more dimensions depending on syntax and mapping metric. You can control which dimensions are replaced or added by adjusting the syntax.**

## Syntax

Syntax is critical when using this parameter as it can change the way the modifier works. `Source_metric_dimension`

, while optional, this parameter defines which dimensions are replaced.

`Source_metric[BY Aggregation method : Source_metric_dimension 1, Source_metric_dimension 2 -> mapping metric]`

`Source_metric`

is the metric that contains the values to be aggregated or allocated.`Allocation method`

(optional) is where you define the way that values are distributed, this is an optional field. If it is not defined, values will be distributed by the Constant method`Aggregation method`

(optional) is where you define the way that values are aggregated. If it is not defined, values will be aggregated using the SUM method.`Source_metric_dimension`

(optional) are the dimensions of the source metric to be replaced. The dimension will be removed. If you are replacing more than one dimension, a comma is needed between them.`mapping metric`

is a metric that defines the relationship between the dimensions in the Source_metric and the dimensions in the target metric. The mapping metric must have a dimension data type.

Return type

`same as source metric`

Table of Contents

## What is a mapping metric?

A mapping metric is used to define a relationship between multiple lists. Similar to when using a mapping attribute a mapping metric must be a dimension data type. The mapping metric’s dimension type must be set to the dimension you want to aggregate or allocate data across. For example, if you wanted to aggregate sales across a `Team`

dimension, your data type for your mapping metric would be `Team`

.

## Source_metric_dimension

The source metric dimension parameter is optional and defines which, if any, dimensions should be replaced by the mapping metric. This parameter must be defined by a dimension used in the source metric. You can use one or more dimensions in this parameter. The dimensions used here must also be present in the mapping metric. For example, if you wanted to replace a `Country`

dimension by a `Team`

dimension, your source and mapping metric must use `Country`

in the metric structure and your data type would be set as `Team`

dimension.

Here is an example of one dimension being replaced.

`Source_metric[BY: Country -> mapping metric]`

In this example, `Country`

is replaced with `Team`

. The mapping metric is located in the top left of this board. Because the mapping metric is dimensionalized by `Products `

and `County`

, you can replace either of these dimensions with `Team`

. When reading this formula, because the dimension is before the → it will be replaced by the dimension used as the data type withing the mapping metric.

Using the same Mapping and Source metric as above, you can also replace `Product `

with `Team `

by adjusting the formula.

`Source_metric[BY: Product -> mapping metric]`

### Adding a dimension with →

If you use the -> but no dimension in the `Source_metric_dimension`

, it will add the dimensions from the mapping, but won't aggregate the data. The added dimension could be seen like a classification: For example, what is the Team in charge for each Product in a Country.

I can also act as a filter. For example. if no Team is in charge, the cell from the source metric won't be reported in the formula result.

You can use → with the BY modifier to bring in an additional dimension to your analysis. In this case we will remove the optional `Source_metric_dimension`

and your formula would be`BY:> mapping metric]`

. When you move this method the data will allocate using the CONSTANT method. This means that it will add the same value across every newly created intersection.

Here is an example where the formula is `Source_metric[BY: -> mapping metric]`

The `Source_metric_dimenision`

parameter is left out and therefore we will add in the dimension from the mapping metric. In this example, the mapping metric is set to a data type of `Team`

. The mapping metric is located in the top left of this board. The mapping metric is dimensionalized by `Products `

and `County`

, which are both present in the source metric. This allows it to add the `Team `

dimension to our source metric. In the resulting metric and the source metric, Months has been added as a page selector for easier visibility.

## Methods when adding a dimension

**Constant **

This method will take the value from the source metric or property and apply it to every item within the replacement dimensions. This is the default behavior when adding a dimension using the ->.

`Source_block[BY: → mapping metric]`

`Source_block[BY CONSTANT: → mapping metric]`

## Aggregation Methods when replacing one or more dimensions

**SUM **

This method returns the sum the source values from aggregated dimension. This is the default behavior when using BY and ->.

`Source_block[BY: mapping attribute ]`

`Source_block[BY SUM: mapping attribute ]`

Supported Data Types - Number, Integer

**AVG**

This method returns the average of the source values from aggregated dimension.

`Source_metric[BY AVG: Source_metric_dimension 1 -> mapping metric]`

Supported Data Types - Number, Integer

### MIN

This method returns the minimum value of the source values from aggregated dimension.

`Source_metric[BY MIN: Source_metric_dimension 1 -> mapping metric]`

Supported Data Types - Number, Integer, Date

### MAX

This method returns the maximum value of the source values from aggregated dimension.

`Source_metric[BY MAX: Source_metric_dimension 1 -> mapping metric]`

Supported Data Types - Number, Integer, Date

### FIRSTNONBLANK

This method returns the first non blank value of the aggregated dimension.

`Source_metric[BY FIRSTNONBLANK: Source_metric_dimension 1 -> mapping metric]`

Supported Data Types - All Types

### FIRSTNONZERO

This method returns the first non zero value of the aggregated dimension.

`Source_metric[BY FIRSTNONZERO: Source_metric_dimension 1 -> mapping metric]`

Supported Data Types - Number, Integer

### FIRST

This method returns the first value of the aggregated dimension.

`Source_metric[BY FIRST: Source_metric_dimension 1 -> mapping metric]`

Supported Data Types - All Types

### LASTNONBLANK

This method returns the last non blank value of the aggregated dimension.

`Source_metric[BY LASTNONBLANK: Source_metric_dimension 1 -> mapping metric]`

Supported Data Types - All Types

### LASTNONZERO

This method returns the last non zero value of the aggregated dimension.

`Source_metric[BY LASTNONZERO: Source_metric_dimension 1 -> mapping metric]`

Supported Data Types - Number, Integer

### LAST

This method returns the last value of the aggregated dimension.

`Source_metric[BY LAST: Source_metric_dimension 1 -> mapping metric]`

Supported Data Types - All Types

### ANY

Returns TRUE if at least one aggregated item is TRUE, else FALSE.

`Source_metric[BY ANY: Source_metric_dimension 1 -> mapping metric]`

Supported Data Types - Boolean

### ALL

Returns TRUE if all aggregated items are TRUE, else FALSE.

`Source_metric[BY ALL: Source_metric_dimension 1 -> mapping metric]`

Supported Data Types - Boolean

### COUNT

Returns the number of aggregated items (BLANK cells are not included).

`Source_metric[BY COUNT: Source_metric_dimension 1 -> mapping metric]`

Supported Data Types - All types

### COUNTBLANK

Returns the number of BLANK items in the aggregated dimension.

`Source_metric[BY COUNTBLANK: Source_metric_dimension 1 -> mapping metric]`

Supported Data Types - All types

### COUNTALL

Returns the number of aggregated items (BLANK cells are included)

`Source_metric[BY COUNTALL: Source_metric_dimension 1 -> mapping metric]`

Supported Data Types - All types

### COUNTUNIQUE

Returns the number of unique values in the aggregated dimension (BLANKS not included)

`Source_metric[BY COUNTUNIQUE: Source_metric_dimension 1 -> mapping metric]`

Supported Data Types - All types

### TEXTLIST

Returns the list of aggregated text values, separated by a comma.

`Source_metric[BY TEXTLIST: Source_metric_dimension 1 -> mapping metric]`

Supported Data Types - Text

**Excel equivalent**: none

**See also**: BY