# Calculated columns **Calculated Column** formulas can be used to perform a wide range of operations on data, from simple arithmetic to complex logical and statistical computations. Whether you're rounding numbers, extracting portions of dates, calculating averages, or transforming texts, these formulas provide the versatility to generate the data you need. Here are some typical use cases for **Calculated Column** functions: 1. **Data Cleaning and Transformation**: Effortlessly clean and reformat data, such as trimming whitespace, changing text casing, or converting date formats. 2. **Statistical Analysis**: Perform statistical calculations like averages, variances, or standard deviations to derive insights from numerical data sets. 3. **Logical Operations**: Apply logical tests to data to create flags, indicators, or to filter and categorize data based on specific criteria. 4. **Mathematical Operations**: Execute a variety of mathematical operations, enabling complex calculations like financial modelling or engineering calculations. 5. **Text and Date Manipulation**: Extract or transform portions of text and date fields, which is particularly useful in data preparation for reporting or further analysis. Note that when a calculated column is applied to generated data, it is processed last. This ensures that all generated data and applied mockers are considered before the calculation occurs, preserving the intended transformations and logic. ## Syntax **Calculated Columns** are composed of a library of functions and operators used to build formulas and expressions in Syntho. When creating **calculated columns** in Syntho, you must follow specific syntax rules and best practices. Here are the fundamental syntax rules for **Calculated Column** expressions when creating **calculated columns**: ### **Formula Structure** Formulas are expressions made from: * **Functions** like `ROUND(...)` or `DATEADD(...)` * **Column references** like `[AMOUNT]` * **Constants** like `2`, `"US"`, or `DATE(2024, 12, 31)` * **Operators** like `+`, `-`, `*`, `/`, `=`, `<>` * **Mockers** like `MOCK_FIRST_NAME` or `MOCK_FREE_EMAIL_DOMAIN` ### **Column references** When referring to a column within the same table, you can just use the column name in square brackets: `[ColumnName]`. Only references to columns within the same tables are supported. ### **Functions** **Calculated Column** includes a wide range of functions that can be used in your expressions, such as time intelligence functions (`DATE`, `DATEADD`), logical functions (`IF`, `AND`, `OR`), and many more. Functions follow the syntax of `FUNCTION_NAME(arguments)`. ### **Mock generators** If you would like to add a mocker to a calculated column formula, type "`MOCK_`" and you will see the names of mockers in the dropdown list. Either choose from the list or add the name of the mocker to the end of "`MOCK_`", with whitespaces replaced by underscores. Therefore, mockers like "**First name female**" and "**Company Email**" should be entered as "`MOCK_FIRST_NAME_FEMALE`" and "`MOCK_COMPANY_EMAIL`," respectively. A complete list of mockers can be found in the [Mockers ](https://docs.syntho.ai/configure-a-data-generation-job/configure-column-settings/mockers)section. When you add a mock generator inside a Calculated Column formula, the settings for that mock generator can be configured in the right-hand screen. Each mock generator you insert into a formula is automatically given a unique identifier by appending an underscore followed by a number (e.g., `MOCK_FIRST_NAME_1`). This identifier allows you to **reuse** the same mock generator value multiple times within the same formula by referring to it via the assigned identifier.

### **Operators** **Calculated Column** supports various operators for arithmetic calculations (`+`, `-`, `*`, `/`), and comparison (`<`, `>`, `<=`, `>=`, `=`, `<>`) operations. ### **Constants** You can use constants like numbers (`123`), strings (`"text"`), and dates (`DATE(2020, 12, 31)`). ### **Whitespace and Line Breaks** Whitespace and line breaks do not affect the interpretation of the formula but can make your formulas easier to read. ### Comments Certain sections in a formula can be commented out by encapsulating them with `(*` and `*)`. These sections will be ignored when processing the formula. This can be useful when you want to add comments to a formula or when are creating larger complex formulas, for which you want to evaluate that certain sections result in the expected output. If you select a formula section and press `CTRL` + `/` the selected section will be commented out. ### Examples #### 1) `ROUND` — round numbers to a fixed number of digits Use `ROUND` when you need consistent decimal precision (for example, currency). ```dax ROUND( [AMOUNT], 2 ) ``` * `number` (number | numeric column): value to round. Example: `[AMOUNT]`. * `count` (integer): number of digits to keep. * `2` keeps two decimals. * `0` rounds to whole numbers. * `-1` rounds to tens, `-2` to hundreds, etc. #### 2) `IF` — return one value when a condition is true, else another Use `IF` to map rows into labels based on a boolean test. ```dax IF( [COUNTRY] = "US", "domestic", "international" ) ``` * `test` (boolean expression): must evaluate to `TRUE` or `FALSE`. Example: `[COUNTRY] = "US"`. * `then_value` (any type): returned when `test` is `TRUE`. Example: `"domestic"`. * `otherwise_value` (any type, optional): returned when `test` is `FALSE`. Example: `"international"`. If omitted, `IF` returns `FALSE`. #### 3) `DATEADD` — shift a date forward or backward Use `DATEADD` to create derived dates like “due date” or “follow-up date”. ```dax DATEADD( [ORDER_DATE], 14, "day" ) ``` * `dates` (date/datetime column): base date to shift. Example: `[ORDER_DATE]`. * `number_of_intervals` (integer): how many units to add or subtract. Example: `14`. Use negative values to subtract. * `interval` (string): unit for the shift. Use `"year"`, `"quarter"`, `"month"`, or `"day"`. #### 4) Invoice total (combined) — tax + discount with null-safe inputs Compute an invoice total that still works when tax rate or discount is missing. ```dax ROUND( ([AMOUNT] * ( 1 + IFNULL( [TAX_RATE], 0 ) )) - IFNULL( [DISCOUNT], 0 ), 2 ) ``` * `IFNULL([TAX_RATE], 0)` falls back to `0` when `[TAX_RATE]` is `NULL`. * `IFNULL([DISCOUNT], 0)` falls back to `0` when `[DISCOUNT]` is `NULL`. * `ROUND(..., 2)` rounds to 2 decimals for currency-like output. #### 5) Normalized email (combined) — conditional separators + normalization + generated domain Build a stable email-like value from name columns, even with missing parts. ```dax SUBSTITUTE( CONCATENATE( LOWER( TRIM( IFNULL( [FIRSTNAME], "" ) ) ), IF( ISNULL([LASTNAME]), "", "." ), LOWER( TRIM( IFNULL( [LASTNAME], "" ) ) ), "@", MOCK_FREE_EMAIL_DOMAIN ), " ", "" ) ``` * `IFNULL(..., "")` prevents `NULL` from nulling the entire result. * `IF(ISNULL([LASTNAME]), "", ".")` only inserts `.` when a last name exists. * `TRIM(...)` removes leading and trailing whitespace. * `LOWER(...)` standardizes casing. * `SUBSTITUTE(..., " ", "")` removes remaining spaces (for double names). {% hint style="info" %} If you want **one generated value reused multiple times** inside a single formula, insert a mocker once and reuse the auto-suffixed name (for example: `MOCK_FREE_EMAIL_DOMAIN_1`). {% endhint %} Examples use existing columns and mockers. Results depend on each input row.

### Limitations & considerations * Column names that begin with an underscore (e.g., `_MyColumn`) will cause “Invalid formula” errors. Rename any columns that start with an underscore so that they begin with a letter or digit instead. * You can only reference columns within the same table as the calculated column formula. Referencing columns in other tables is not supported. ## Supported data types | Generator | Supported data types | | ------------------ | --------------------------------------- | | Calculated Columns | Categorical, Discrete, Continuous, Date | ## Supported calculated column functions ## ABS Returns the absolute value of a number. ### Syntax ```dax ABS(number) ``` #### Parameters

Parameter	Description
number	Any number, column or mock value.

### Return value An absolute value of the number. ### Examples

Formula	Description	Result
`ABS( [HEALTHCARE_COVERAGE] )`	Returns the absolute value of the `[HEALTHCARE_COVERAGE]` column for each row.	Varies by row (e.g., `[HEALTHCARE_COVERAGE]=-20` → `20`).
`ABS( -5 )`	Returns the absolute value of a literal number.	`5`
`ABS( MOCK_RANDOM_DIGIT )`	Returns the absolute value of a generated random digit.	Varies by row (e.g., `MOCK_RANDOM_DIGIT=7` → `7`).
`ABS( [HEALTHCARE_COVERAGE] - [HEALTHCARE_EXPENSES] )`	Returns the absolute difference between `[HEALTHCARE_COVERAGE]` and `[HEALTHCARE_EXPENSES]` for each row.	Varies by row (e.g., `[HEALTHCARE_COVERAGE]=250` and `[HEALTHCARE_EXPENSES]=100` → `150`).
`IF( ABS([HEALTHCARE_COVERAGE] - [HEALTHCARE_EXPENSES]) > 100, "outlier", "ok" )`	Flags rows where the absolute difference between coverage and expenses exceeds `100`.	Either `"outlier"` or `"ok"` per row.

## AND The `AND` function returns `TRUE` if all provided arguments evaluate to `TRUE`, and `FALSE` if any argument evaluates to `FALSE`. A common use case for the `AND` function is enhancing other functions that perform logical tests. For instance, the `IF` function tests a condition and returns one value if the condition is `TRUE` and another value if it is `FALSE`. By using `AND` as the `logical_test` argument in the `IF` function, you can test multiple conditions simultaneously rather than just one. ### Syntax ```dax AND( Logicalvalue1, Logicalvalue2, ...Logicalvalue30 ) ``` #### Parameters

Parameter	Description
Logicalvalue1	The first condition to evaluate, which must result in either `TRUE` or `FALSE`.
Logicalvalue2	Additional conditions to evaluate, up to a maximum of 30, each of which must result in either `TRUE` or `FALSE`.

### Return value Returns TRUE if all arguments are TRUE. ### Examples

Formula	Description	Result
`AND( [GENDER] = "M", [HEALTHCARE_COVERAGE] > 100 )`	Checks if gender is male and healthcare coverage exceeds `100`.	`TRUE` or `FALSE` per row.
`AND( [STATE] = "Massachusetts", [HEALTHCARE_COVERAGE] > 100, [HEALTHCARE_EXPENSES] < 500 )`	Checks three conditions at once: state, minimum coverage, and maximum expenses.	`TRUE` or `FALSE` per row.
`IF( AND( [AGE] >= 18, [AGE] < 65 ), "adult", "senior_or_minor" )`	Classifies rows as `"adult"` when age is between 18 and 64 inclusive.	Either `"adult"` or `"senior_or_minor"` per row.
`AND( NOT([IS_ACTIVE] = 0), [STATUS] <> "Deleted" )`	Checks that a row is active and not marked as deleted.	`TRUE` or `FALSE` per row.
`IF( AND( ISNULL([ZIP]), [COUNTRY] = "US" ), "missing_us_zip", "ok" )`	Flags US rows with a missing ZIP code.	Either `"missing_us_zip"` or `"ok"` per row.

## BASE Converts a number into its text representation in the specified radix (base). ### Syntax ```dataweave BASE(number, Radix, MinimumLength) ``` #### Parameters

Parameter	Description
number	The number, column or mock value to be converted. It must be an integer greater than or equal to 0 and less than 2^53.
radix	The base (radix) to convert the number into. It must be an integer between 2 and 36, inclusive.
MinimumLength	The minimum length of the returned string. It must be an integer greater than or equal to 0.

### Return value Converts a positive integer to a specified base into a text from the numbering system. ### Examples

Formula	Description	Result
`BASE( 5, 2 )`	Converts the decimal number `5` to base `2` (binary).	`101`
`BASE( 5, 2, 8 )`	Converts `5` to base `2` and left-pads with zeros to length `8`.	`00000101`
`BASE( 42, 16 )`	Converts the decimal number `42` to base `16` (hexadecimal).	`2A`
`BASE( [NUMBER], [RADIX] )`	Converts the `[NUMBER]` column to the base specified in `[RADIX]` for each row.	Varies by row.
`BASE( RANDBETWEEN(0, 9), RANDBETWEEN(2, 36) )`	Converts a random digit using a random radix. Radix must be between `2` and `36`.	Varies by row (or errors if radix is out of range).
`BASE( [NUMBER], 36, 6 )`	Converts `[NUMBER]` to base `36` and pads the output to length `6`.	Varies by row.
`CONCATENATE( "acct_", UPPER( BASE( [ACCOUNT_ID], 16, 8 ) ) )`	Builds an account-like identifier by converting `[ACCOUNT_ID]` to padded hex and prefixing with `acct_`.	Varies by row.

## CEILING Rounds a number up, away from zero, to the nearest multiple of a specified significance. The function always rounds a value up, away from zero, regardless of the sign of the number. If the number is an exact multiple of the significance, no rounding is performed. If both the number and significance are negative, the value is rounded down, away from zero. If the number is negative and the significance is positive, the value is rounded up toward zero. ### Syntax ```dax CEILING(number, Significance) ``` #### Parameters

Parameter	Description
number	The column, number or mock value to be rounded.
significance	The multiple to which you want to round the number.

### Return value A number rounded up to the nearest multiple of Significance. ### Examples

Formula	Description	Result
`CEILING( 3.1, 1 )`	Rounds `3.1` up to the nearest multiple of `1`.	`4`
`CEILING( -4.5, -2 )`	Rounds `-4.5` to the nearest multiple of `-2`, away from zero.	`-6`
`CEILING( -2.5, 2 )`	Rounds `-2.5` to the nearest multiple of `2`, toward zero.	`-2`
`CEILING( 5.5, 0.1 )`	Rounds `5.5` up to the nearest multiple of `0.1`.	`5.5`
`CEILING( 0.9345, 0.001 )`	Rounds `0.9345` up to the nearest multiple of `0.001`.	`0.935`
`CEILING( ABS([AMOUNT]), 0.05 )`	Rounds the absolute amount up to the nearest `0.05`.	Varies by row.
`IF( [AMOUNT] < 0, -CEILING( ABS([AMOUNT]), 1 ), CEILING( [AMOUNT], 1 ) )`	Rounds amounts to whole numbers while keeping the original sign.	Varies by row.

## CLEAN Removes all nonprintable characters from a given text. The `CLEAN` function is useful for cleaning text imported from other applications that may contain characters not supported by your operating system. For example, it can remove low-level computer code that often appears at the beginning or end of data files and cannot be printed. {% hint style="info" %} **Important** The `CLEAN` function is designed to remove the first 32 nonprinting characters in the 7-bit ASCII code (values 0 through 31). However, in the Unicode character set, there are additional nonprinting characters (values 127, 129, 141, 143, 144, and 157) that the `CLEAN` function does not remove. {% endhint %} ### Syntax ```dart CLEAN(text) ``` #### Parameters

Parameter	Description
text	The text, column or mock values from which you want to remove nonprintable characters.

### Return value The text that has been "cleaned" of line breaks and other non-printable characters. ### Examples

Formula	Description	Result
`CLEAN( CHAR(3) & "Patient Medications" & CHAR(17) )`	Removes non-printable characters from the string, leaving only readable text.	`"Patient Medications"`
`CLEAN( CHAR(3) & [description] & CHAR(17) )`	Cleans non-printable characters around the `[description]` column value.	Varies by row.
`CLEAN( CHAR(3) & MOCK_FIRST_NAME & CHAR(17) )`	Cleans non-printable characters around a generated first name.	Varies by row.
`CLEAN( TRIM([DESCRIPTION]) )`	Trims whitespace and removes non-printable characters from `[DESCRIPTION]`.	Varies by row.
`CLEAN( CONCATENATE( MOCK_FIRST_NAME, " ", MOCK_LAST_NAME ) )`	Joins a generated first and last name and removes any non-printable characters.	Varies by row.

## CONCATENATE The `CONCATENATE` function joins two or more text strings into a single string. ### Syntax ```dax CONCATENATE("Text1", ..., "Text30") ``` #### Parameters

Parameter	Description
text1	The first item to join. This can be a text, column or mock values.
text2, ...text30	Additional text items to join. You can include up to 30 items, with a total character limit of 8,192.

### Return value Several text strings combined into one string. ### Examples

Formula	Description	Result
`CONCATENATE( [STATE], ", ", [COUNTY] )`	Joins state and county into a single string per row.	Varies by row (e.g., `"Massachusetts, Bristol County"`).
`CONCATENATE( MOCK_FIRST_NAME_FEMALE, " ", MOCK_LAST_NAME_FEMALE )`	Builds a full name using mock generators.	Varies by row.
`CONCATENATE( "Brittany", " ", "Jenkins" )`	Joins literal strings into a full name.	`"Brittany Jenkins"`
`CONCATENATE( UPPER([STATE]), "-", [COUNTY] )`	Uppercases the state and joins it with the county, separated by `-`.	Varies by row (e.g., `"MASSACHUSETTS-Bristol County"`).
`CONCATENATE( LOWER( TRIM([FIRSTNAME]) ), ".", LOWER( TRIM([LASTNAME]) ), "@", MOCK_FREE_EMAIL_DOMAIN )`	Builds a lowercase email-like string from name columns plus a generated domain.	Varies by row.

## DATE The `DATE` function creates a date value by combining individual year, month, and day components. This function is useful for combining separate year, month, and day values into a single date. ### Syntax ```dax DATE(year, month, day) ``` #### Parameters

Parameter	Description
year	A number, column or mock value representing the year.
month	A number, column or mock value column representing the month of the year.
day	A number, column or mock value column representing the day of the month.

### Return value The specified date as the number of full days since nullDate. ### Examples The following examples build date values from literal values, columns, and other functions.

Formula	Description	Result
`DATE( 2012, 8, 24 )`	Creates the date for August 24, 2012.	`2012-08-24`
`DATE( "2012", "8", "24" )`	Creates the same date, but from string inputs.	`2012-08-24`
`DATE( [year], [month], [day] )`	Creates a date per row from the `[year]`, `[month]`, and `[day]` columns.	Varies by row.
`DATE( YEAR(TODAY()) - 18, 1, 1 )`	Creates January 1st of “current year - 18”.	Varies by run date.
`DATE( [YEAR], MONTH(TODAY()), 1 )`	Creates the first day of the current month, using `[YEAR]` per row.	Varies by row and run date.

## DATEADD Returns a column of dates, shifted forward or backward by the specified number of intervals. ### Syntax ```dax DATEADD(dates, number_of_intervals, interval) ``` #### Parameters

Parameter	Description
dates	A column or mocker that contains dates.
number_of_intervals	An integer specifying the number of intervals to add or subtract from the dates.
interval	The interval used to shift the dates. Options include: `year`, `quarter`, `month`, or `day`.

### Return value A column of date values. ### Examples

Formula	Description	Result
`DATEADD( [Birthdate], 20, "year" )`	Shifts `[Birthdate]` 20 years forward.	Varies by row.
`DATEADD( [Birthdate], -6, "month" )`	Shifts `[Birthdate]` 6 months backward.	Varies by row.
`DATEADD( [OrderDate], 14, "day" )`	Shifts `[OrderDate]` 14 days forward.	Varies by row.
`DATEADD( DATEVALUE([ORDER_DATE_TEXT]), 7, "day" )`	Parses a text date first, then shifts it 7 days forward.	Varies by row.
`DATEADD( [BIRTHDATE], IF( [IS_LEAP_YEAR] = 1, 1, 0 ), "year" )`	Shifts `[BIRTHDATE]` by 0 or 1 year depending on `[IS_LEAP_YEAR]`.	Varies by row.

## DATEVALUE #### Description The `DATEVALUE` function converts a date stored as text into a date. Note that the results of the `DATEVALUE` function may vary depending on your computer's system date settings. The `DATEVALUE` function is useful when you have dates in a text format that you want to filter, sort, format as dates, or use in date calculations. ### Syntax ```dax DATEVALUE(date_text) ``` #### Parameters

Parameter	Description
date_text	A text column, string or mock representing a date.

### Return value A date that is converted from text to a date in datetime format. ### Examples

Formula	Description	Result
`DATEVALUE( [BIRTHDATE] )`	Converts `[BIRTHDATE]` stored as text into a date per row.	Varies by row.
`DATEVALUE("6/24/2012")`	Converts a literal date string into a date value (system locale dependent).	A date value.
`DATEVALUE("2012/6/24")`	Converts a literal date string into a date value (system locale dependent).	A date value.
`DATEVALUE("24-JUNE-2024")`	Converts a literal date string into a date value.	`2024-06-24`
`YEAR( DATEVALUE([BIRTHDATE_TEXT]) )`	Extracts the year after converting a text date column.	Varies by row.
`DATEADD( DATEVALUE([START_DATE_TEXT]), 30, "day" )`	Converts `[START_DATE_TEXT]` to a date, then shifts it 30 days forward.	Varies by row.

## DAY The `DAY` function returns the day of a date represented by a serial number, with the day provided as an integer ranging from 1 to 31. ### Syntax ```dax DAY(number) ``` #### Parameters

Parameter	Description
number	The date, column or mock date for which you want to find the day. Dates should be entered using the `DATE` function or as results from other formulas or functions.

### Return value The day of the given date value. ### Examples

Formula	Description	Result
`DAY("24/6/2012")`	Returns the day-of-month from a literal date string.	`24`
`DAY([BIRTHDATE])`	Returns the day-of-month for `[BIRTHDATE]` per row.	Varies by row.
`DAY(DATE(2012, 8, 24))`	Returns the day-of-month for a constructed date.	`24`
`DAY( DATEADD( [BIRTHDATE], 1, "year" ) )`	Returns the day-of-month after shifting `[BIRTHDATE]` 1 year forward.	Varies by row.
`IF( DAY([INVOICE_DATE]) = 1, "month_start", "other_day" )`	Flags whether `[INVOICE_DATE]` falls on the first day of a month.	Either `"month_start"` or `"other_day"` per row.

## DAYS The `DAYS` function calculates the number of days between two dates. ### Syntax ```dataweave DAYS(date1, date2) ``` #### Parameters

Parameter	Description
date1	The later of the two dates column between which you want to calculate the number of days.
date2	The earlier of the two dates column between which you want to calculate the number of days.

### Return value The difference between two date values. ### Examples

Formula	Description	Result
`DAYS( "24/6/2012", "14/6/2012" )`	Returns the number of days between two literal dates.	`10`
`DAYS( [END_DATE], [START_DATE] )`	Returns days between two date columns per row.	Varies by row.
`DAYS( DATE(2024, 12, 31), DATE(2024, 1, 1) )`	Returns the days between Jan 1 and Dec 31, 2024.	`365`
`ABS( DAYS([END_DATE], [START_DATE]) )`	Returns the absolute number of days between two date columns.	Varies by row.
`IF( DAYS([END_DATE], [START_DATE]) <= 30, "short_stay", "long_stay" )`	Classifies stays as short (≤ 30 days) or long.	Either `"short_stay"` or `"long_stay"` per row.

## DATEDIFF The `DateDiff` function returns the number of interval units between two dates. ### Syntax ```dax DATEDIFF(date1, date2, interval) ``` #### Parameters

Parameter Description

date1, date2 An integer specifying the number of intervals to add or subtract from the dates.

interval

Parameter	Description
date1, date2	An integer specifying the number of intervals to add or subtract from the dates.
interval	A string expression that specifies the time interval you want to use to calculate the difference between `date1` and `date2`. The value can be one of the following: "second" "minute" "hour" "day" "week" "month" "quarter" "year"

A string expression that specifies the time interval you want to use to calculate the difference between date1 and date2.

The value can be one of the following:

"second"
"minute"
"hour"
"day"
"week"
"month"
"quarter"
"year"

### Return value The number of units between two dates as defined in the interval. ### Examples

Formula	Description	Result
`DATEDIFF( [EXPIRY_DATE], [INITIAL_DATE], "day" )`	Returns the number of days between `[EXPIRY_DATE]` and `[INITIAL_DATE]` per row.	Varies by row.
`DATEDIFF( [BIRTHDATE], TODAY(), "year" )`	Returns the age in years, based on `[BIRTHDATE]`.	Varies by row and run date.
`DATEDIFF( DATE(2024, 1, 1), DATE(2024, 2, 1), "month" )`	Returns the number of months between Jan 1 and Feb 1, 2024.	`1`
`DATEDIFF( DATEVALUE([START_DATE_TEXT]), DATEVALUE([END_DATE_TEXT]), "day" )`	Parses two text dates first, then returns the days between them.	Varies by row.
`IF( DATEDIFF([SIGNUP_DATE], TODAY(), "day") <= 30, "new", "existing" )`	Classifies customers as `"new"` if they signed up in the last 30 days.	Either `"new"` or `"existing"` per row.

## DELTA The `DELTA` function checks whether two values are equal. It returns `1` if `number1` equals `number2`, and `0` otherwise. ### Syntax ```dataweave DELTA(number_1, number_2) ``` #### Parameters

Parameter	Description
number_1	The first number, column or mocker.
number_2	The second number, column or mocker. If omitted, `column2` is assumed to be zero.

### Return value Returns TRUE (1) if both numbers are equal, otherwise returns FALSE (0). ### Examples

Formula	Description	Result
`DELTA( [HEALTHCARE_COVERAGE], [HEALTHCARE_EXPENSES] )`	Returns `1` when `[HEALTHCARE_COVERAGE]` equals `[HEALTHCARE_EXPENSES]`, else `0`.	`0` or `1` per row.
`DELTA( 10, 10 )`	Checks equality of two literal numbers.	`1`
`DELTA( [COPAY_AMOUNT] )`	Checks whether `[COPAY_AMOUNT]` equals `0` (second argument defaults to `0`).	`0` or `1` per row.
`IF( DELTA([STATUS_CODE], 0) = 1, "unknown", "known" )`	Maps rows to `"unknown"` when `[STATUS_CODE]` equals `0`.	Either `"unknown"` or `"known"` per row.
`DELTA( ROUND([AMOUNT], 0), ROUND([AMOUNT_APPROVED], 0) )`	Checks equality after rounding `[AMOUNT]` and `[AMOUNT_APPROVED]` to whole numbers.	`0` or `1` per row.

## EVEN The `EVEN` function rounds a number up to the nearest even integer. The function always rounds a value up and away from zero, regardless of its sign. If the `number column` is already an even integer, no rounding occurs. ### Syntax ```dax EVEN(number) ``` #### Parameters

Parameter	Description
number	The number, column or mocker to be rounded up to the nearest even integer.

### Return value A positive number rounded up to the next even integer and a negative number rounded down to the next even integer. ### Examples

Formula	Description	Result
`EVEN( [HEALTHCARE_COVERAGE] )`	Rounds `[HEALTHCARE_COVERAGE]` up (away from zero) to the nearest even integer per row.	Varies by row.
`EVEN( 3.2 )`	Rounds `3.2` up to the next even integer.	`4`
`EVEN( -3.2 )`	Rounds `-3.2` down (away from zero) to the next even integer.	`-4`
`EVEN( ROUND([HEALTHCARE_COVERAGE], 0) )`	Rounds `[HEALTHCARE_COVERAGE]` to a whole number first, then to the nearest even integer.	Varies by row.
`IF( ISODD([SEAT_NUMBER]) = 1, EVEN([SEAT_NUMBER] + 1), [SEAT_NUMBER] )`	Makes odd `[SEAT_NUMBER]` values even by adding `1`; leaves even values unchanged.	Varies by row.

## FALSE Returns the logical value FALSE. ### Syntax ```dax FALSE() ``` ### Return value The logical value FALSE. ### Examples

Formula	Description	Result
`FALSE()`	Returns the logical value `FALSE`.	`FALSE`
`IF( [IS_ACTIVE] = 1, TRUE(), FALSE() )`	Returns `TRUE` when `[IS_ACTIVE]` equals `1`; otherwise returns `FALSE`.	`TRUE` or `FALSE` per row.
`IF( FALSE(), "never", "always" )`	Demonstrates that a constant `FALSE()` condition always returns the `otherwise` value.	`"always"`
`AND( FALSE(), [IS_ACTIVE] = 1 )`	Shows that `AND` returns `FALSE` when any argument is `FALSE`.	`FALSE`
`IF( FALSE(), MOCK_FIRST_NAME, MOCK_LAST_NAME )`	Always returns the `otherwise` branch, so it generates last names.	Varies by row.

## FLOOR The `FLOOR` function rounds a number down toward zero to the nearest multiple of a specified significance. When the `number` is positive, it is rounded down and adjusted toward zero. When the `number` is negative, it is rounded down and adjusted away from zero. If the `number` is already an exact multiple of the `significance`, no rounding occurs. * If either argument is nonnumeric, the function returns an error message. * If the `number` is positive and the `significance` is negative, the function returns an error message. ### Syntax ```dax FLOOR(number, significance) ``` #### Parameters

Parameter	Description
number	The numeric number, column or mocker you want to round down.
significance	The multiple to which you want to round the number.

### Return value Rounds a number down to the nearest multiple of Significance. ### Examples The following formulas round numbers down to the nearest multiple of Significance.

Formula	Description	Result
`FLOOR( 7.7, 3 )`	Rounds `7.7` down to the nearest multiple of `3`.	`6`
`FLOOR( -3.4, -3 )`	Rounds `-3.4` down to the nearest multiple of `-3`.	`-3`
`FLOOR( 1.453, 0.01 )`	Rounds `1.453` down to the nearest multiple of `0.01`.	`1.45`
`FLOOR( [AMOUNT], 1 )`	Rounds `[AMOUNT]` down to the nearest whole number per row.	Varies by row.
`FLOOR( ABS([AMOUNT]), 0.5 )`	Rounds the absolute amount down to the nearest multiple of `0.5`.	Varies by row.

## HEX2DEC The `HEX2DEC` function converts a hexadecimal number to its decimal equivalent. If the input is not a valid hexadecimal number, the function returns an error. ### Syntax ```dataweave HEX2DEC(number) ``` #### Parameters

Parameter	Description
number	The hexadecimal number, column or mocker column to convert. The number can have up to 10 characters (40 bits). The most significant bit represents the sign, and the remaining 39 bits represent the magnitude. Negative numbers are represented using two's-complement notation.

### Return value The decimal number for the hexadecimal number entered. ### Examples

Formula	Description	Result
`HEX2DEC( "CAB007" )`	Converts a literal hex string to its decimal value.	`13283335`
`HEX2DEC( "FF" )`	Converts `"FF"` (hex) to decimal.	`255`
`HEX2DEC( [HEX_VALUE] )`	Converts the hex value in `[HEX_VALUE]` to decimal per row.	Varies by row.
`HEX2DEC( UPPER([HEX_VALUE]) )`	Uppercases `[HEX_VALUE]` first, then converts to decimal.	Varies by row.
`HEX2DEC( RIGHT([HEX_PAYLOAD], 6) )`	Extracts the last 6 hex characters from `[HEX_PAYLOAD]`, then converts to decimal.	Varies by row.

## HOUR The `HOUR` function returns the hour component of a time value as an integer, ranging from 0 (12:00 A.M.) to 23 (11:00 P.M.). Time values are part of a date value and are represented as decimal numbers (e.g., 12:00 PM is represented as `0.5` because it is half of a day). ### Syntax ```dax HOUR(time) ``` #### Parameters

Parameter	Description
time	The time number, column or mocker from which you want to extract the hour. This can be entered as a text string within quotation marks (e.g., `"6:45 PM"`), as a decimal number (e.g., `0.78125` representing 6:45 PM), or as the result of other formulas or functions (e.g., `TIMEVALUE("6:45 PM")`).

### Return value Hour component of given time. ### Examples

Formula	Description	Result
`HOUR( "6/24/2012 7:30" )`	Extracts the hour from a literal datetime string.	`7`
`HOUR( [START] )`	Extracts the hour from the `[START]` datetime column per row.	Varies by row.
`HOUR( "6:45 PM" )`	Extracts the hour from a literal time string.	`18`
`IF( HOUR([START]) >= 9, "business_hours", "off_hours" )`	Classifies rows based on whether `[START]` occurs at 09:00 or later.	Either `"business_hours"` or `"off_hours"` per row.
`HOUR( DATEADD([START], 1, "day") )`	Adds one day to `[START]` and extracts the hour (same hour, shifted date).	Varies by row.

## IF The `IF` function performs a logical test and returns one value if the test evaluates to TRUE and another value if it evaluates to FALSE. This function is commonly used to create conditional formulas. ### Syntax ```dax IF(test, then value, otherwisevalue) ``` #### Parameters

Parameter	Description
test	The condition you want to test. This can be any expression that returns either TRUE or FALSE.
then value	The column or mock value that is returned if the `logical_test` evaluates to TRUE.
otherwisevalue	The column or mock value that is returned if the `logical_test` evaluates to FALSE. If omitted, the function returns FALSE by default.

### Return value A logical test result. ### Examples

Formula	Description	Result
`IF( AND( [STATE] = "Massachusetts", [HEALTHCARE_COVERAGE] > 30 ), MOCK_FIRST_NAME_FEMALE, MOCK_FIRST_NAME_MALE )`	Generates a female first name for MA rows with coverage over `30`; otherwise generates a male first name.	Varies by row.
`IF( [HEALTHCARE_COVERAGE] > 0, [HEALTHCARE_COVERAGE] )`	Returns `[HEALTHCARE_COVERAGE]` when it’s positive; otherwise returns `FALSE` (default `otherwise`).	Either a number or `FALSE` per row.
`IF( [ZIP] = "0000XX", "unknown", "known" )`	Maps the sentinel ZIP value `"0000XX"` to `"unknown"`, otherwise `"known"`.	Either `"unknown"` or `"known"` per row.
`IF( DATEDIFF([BIRTHDATE], TODAY(), "year") >= 18, "adult", "minor" )`	Classifies rows as `"adult"` when age is 18+; otherwise `"minor"`.	Either `"adult"` or `"minor"` per row.
`IF( ISNULL([MIDDLE_NAME]), CONCATENATE( [FIRSTNAME], " ", [LASTNAME] ), CONCATENATE( [FIRSTNAME], " ", [MIDDLE_NAME], " ", [LASTNAME] ) )`	Builds a full name, optionally inserting `[MIDDLE_NAME]` when present.	Varies by row.

## IFS The `IFS` function checks multiple conditions and returns a value corresponding to the first condition that evaluates to TRUE. This function simplifies the use of multiple nested `IF` statements, making it easier to read and manage multiple conditions. To specify a default result, enter `TRUE` as the final condition argument. If none of the other conditions are met, the corresponding value will be returned. For example, if all other conditions fail, a default value can be returned by using `TRUE` as the final condition. If a condition argument is provided without a corresponding value, the function will return an error message. If a condition argument evaluates to something other than TRUE or FALSE, the function returns an error. If none of the conditions evaluate to TRUE, the function returns an error. ### Syntax ```dataweave IFS(condition1, number1, condition2, number2..., condition_n, number_N) ``` #### Parameters

Parameter	Description
condition1	The condition that is evaluated to determine if it is TRUE or FALSE.
number1	The column or mock value returned if `condition1` evaluates to TRUE. This can be left empty.
condition2…condition_N	Additional conditions that are evaluated to determine if they are TRUE or FALSE.
number2 …number_N	Th column or mock value returned if the corresponding `conditionN` evaluates to TRUE. Each `valueN` is associated with its corresponding `conditionN`. These can also be left empty.

### Return value A value that corresponds to the first true condition. ### Examples

Formula	Description	Result
`IFS( [GENDER] = "F", MOCK_NAME_FEMALE, [GENDER] = "M", MOCK_NAME_MALE )`	Generates a mock female name for `"F"` and a mock male name for `"M"`.	Varies by row (or errors if no condition matches).
`IFS( [STATE] = "Massachusetts", "MA", [STATE] = "California", "CA", TRUE, "OTHER" )`	Maps states to abbreviations, with `"OTHER"` as a default.	`"MA"`, `"CA"`, or `"OTHER"` per row.
`IFS( [SCORE] >= 90, "A", [SCORE] >= 80, "B", [SCORE] >= 70, "C", TRUE, "D" )`	Assigns a grade based on score thresholds.	`"A"`, `"B"`, `"C"`, or `"D"` per row.
`IFS( AND( [COUNTRY] = "US", ISNULL([STATE]) = FALSE ), "US_WITH_STATE", [COUNTRY] = "US", "US_MISSING_STATE", TRUE, "NON_US" )`	Classifies US/non-US rows and whether a US row has a state value.	`"US_WITH_STATE"`, `"US_MISSING_STATE"`, or `"NON_US"` per row.
`IFS( LEN(TRIM([PHONE])) < 10, "invalid", TRUE, "valid" )`	Validates phone numbers by trimmed length, with a default `"valid"`.	`"invalid"` or `"valid"` per row.

## IFNULL Returns replacement\_value when value is NULL; otherwise returns value. ### Syntax ```excel-formula IFNULL(value, replacement_value) ``` #### Parameters

Parameter	Description
value	The value, column or generator you want to check for NULL.
replacement	The value, column or generator to replace a NULL value with.

### Return value Returns the specified replacement value if the observed value is NULL. ### Examples

Formula	Description	Result
`IFNULL( [ZIP], "0000XX" )`	Replaces missing ZIP codes with `"0000XX"`.	`[ZIP]` or `"0000XX"` per row.
`IFNULL( [MIDDLE_NAME], "" )`	Replaces a missing middle name with an empty string.	`[MIDDLE_NAME]` or `""` per row.
`IFNULL( MOCK_POSTCODE, "0000XX" )`	Replaces a missing generated postcode value with a fallback.	Varies by row.
`IFNULL( TRIM([CITY]), "Unknown" )`	Trims `[CITY]`, then replaces a missing value with `"Unknown"`.	`[CITY]` (trimmed) or `"Unknown"` per row.
`IF( ISNULL([ZIP]), "0000XX", IFNULL([ZIP], "0000XX") )`	Uses `ISNULL` and `IFNULL` together to guarantee a ZIP fallback.	`[ZIP]` or `"0000XX"` per row.

## ISNULL The `ISNULL` function checks whether a value is NULL and returns `TRUE` if the cell is NULL. If the value contains any data, including a formula that returns an empty string (`""`), `ISNULL` returns `FALSE`. ### Syntax ```excel-formula ISNULL(value) ``` #### Parameters

Parameter	Description
value	The value, column or mocker you want to check for NULL.

### Return value `TRUE` if the value is NULL; `FALSE` if it contains any data. ### Examples

Formula	Description	Result
`ISNULL([ZIP])`	Checks whether `[ZIP]` is NULL.	`TRUE` or `FALSE` per row.
`ISNULL([MIDDLE_NAME])`	Checks whether `[MIDDLE_NAME]` is NULL.	`TRUE` or `FALSE` per row.
`IF( ISNULL([ZIP]), "missing", "present" )`	Maps missing ZIP codes to `"missing"`, otherwise `"present"`.	Either `"missing"` or `"present"` per row.
`NOT(ISNULL([EMAIL]))`	Returns `TRUE` when `[EMAIL]` is present (not NULL).	`TRUE` or `FALSE` per row.
`AND( ISNULL([PHONE]), ISNULL([MOBILE_PHONE]) )`	Checks whether both phone fields are NULL.	`TRUE` or `FALSE` per row.

## ISODD The `ISODD` function checks whether a number is odd. It returns `1` if the number is odd, and `0` if the number is even. This function is useful for determining the parity of a number within a table. If the number is nonnumeric, `ISODD` returns an error. ### Syntax ```dax ISODD(value) ``` #### Parameters

Parameter	Description
value	The value or column to test. If the number is not an integer, it is truncated.

### Return value `0` if the value is an even integer; `1` if the value is odd. ### Examples

Formula	Description	Result
`ISODD([HEALTHCARE_EXPENSES])`	Checks whether `[HEALTHCARE_EXPENSES]` is odd.	`0` or `1` per row.
`ISODD(7)`	Checks whether the literal number `7` is odd.	`1`
`ISODD([HEALTHCARE_EXPENSES] + 1)`	Adds `1` to the expenses value, then checks if the result is odd.	`0` or `1` per row.
`IF( ISODD([INVOICE_ID]) = 1, "odd_id", "even_id" )`	Labels invoice ids by parity.	Either `"odd_id"` or `"even_id"` per row.
`ISODD( QUOTIENT( [TOTAL_MINUTES], 60 ) )`	Converts minutes to whole hours and checks if the hour count is odd.	`0` or `1` per row.

## LEFT The `LEFT` function returns a specified number of characters from the left side of a string. To determine the number of characters in a string, use the `LEN` function. ### Syntax ```dax LEFT("text", number) ``` #### Parameters

Parameter	Description
"text"	The string, column or mocker from which the leftmost characters are returned. If the string contains `Null`, the function returns `Null`.
number	A numeric expression or column indicating how many characters to return. If `0`, a zero-length string (`""`) is returned. If the length is greater than or equal to the number of characters in the string, the entire string is returned.

### Return value The input text capped by a specified character length. ### Examples

Formula	Description	Result
`LEFT([STATE], MOCK_RANDOM_DIGIT)`	Returns a random-length left substring from `[STATE]` per row.	Varies by row.
`LEFT("Massachusetts", 4)`	Returns the first 4 characters of a literal string.	`"Mass"`
`LEFT([STATE], 0)`	Returns the first 0 characters, which is an empty string.	`""`
`LEFT(TRIM([STATE]), 2)`	Trims `[STATE]` first, then returns the first 2 characters.	Varies by row (e.g., `"Ma"`).
`LEFT(CONCATENATE([CITY], ", ", [STATE]), 12)`	Builds `"City, State"` then returns the first 12 characters.	Varies by row.

## LEN The `LEN` function returns a `Long` value representing the number of characters in a string or the number of bytes required to store a variable. ### Syntax ```dax LEN("text") ``` #### Parameters

Parameter	Description
"text"	Any valid string expression, column or mocker.

### Return value Length of a given text. ### Examples

Formula	Description	Result
`LEN([BIRTHPLACE])`	Returns the number of characters in `[BIRTHPLACE]` per row.	Varies by row.
`LEN("Syntho")`	Returns the number of characters in a literal string.	`6`
`LEN(CONCATENATE([CITY], ", ", [STATE]))`	Builds `"City, State"` then returns the character count.	Varies by row.
`LEN(TRIM([ADDRESS]))`	Trims whitespace from `[ADDRESS]` then returns the character count.	Varies by row.
`IF(LEN([PHONE]) < 10, "invalid", "valid")`	Validates phone numbers based on their length.	Either `"invalid"` or `"valid"` per row.

## LN The `LN` function returns the natural logarithm of a specified number. Natural logarithms are based on the mathematical constant `e` (approximately 2.71828182845904). The `LN` function is the inverse of the `EXP` function. ### Syntax ```dax LN(number) ``` #### Parameters

Parameter	Description
number	The positive real number, column or mocker for which you want to calculate the natural logarithm.

### Return value The natural logarithm of the number. ### Examples

Formula	Description	Result
`LN([HEALTHCARE_EXPENSES])`	Returns the natural log of `[HEALTHCARE_EXPENSES]` per row.	Varies by row (and errors for non-positive values).
`LN(10)`	Returns the natural log of a literal number.	`~2.3026`
`LN([HEALTHCARE_EXPENSES] + 1)`	Adds `1` to avoid `LN(0)` for zero values, then logs the result.	Varies by row.
`LN(ABS([HEALTHCARE_EXPENSES]))`	Takes the absolute value first, then computes the natural log.	Varies by row (and errors when the absolute value is `0`).
`IF([HEALTHCARE_EXPENSES] > 0, LN([HEALTHCARE_EXPENSES]), 0)`	Computes `LN` only for positive values; otherwise returns `0`.	Either `0` or a log value per row.

## LOG The `LOG` function returns the logarithm of a specified number to a base that you define. ### Syntax ```dax LOG(number, base) ``` #### Parameters

Parameter	Description
number	The positive real number, column or mocker for which you want to calculate the logarithm.
base	The base of the logarithm. If omitted, the base is assumed to be 10.

### Return value The logarithm of a number to the specified base. ### Examples

Formula	Description	Result
`LOG([HEALTHCARE_EXPENSES], 2)`	Returns the base-2 log of `[HEALTHCARE_EXPENSES]` per row.	Varies by row (and errors for non-positive values).
`LOG(100)`	Returns the base-10 log of `100` (default base).	`2`
`LOG([HEALTHCARE_EXPENSES], [LOG_BASE])`	Returns the log using a per-row base from `[LOG_BASE]`.	Varies by row.
`LOG([HEALTHCARE_EXPENSES])`	Returns the base-10 log of `[HEALTHCARE_EXPENSES]` per row.	Varies by row.
`ROUND(LOG([HEALTHCARE_EXPENSES], 2), 3)`	Computes base-2 log and rounds it to 3 decimals.	Varies by row.

## LOG10 The `LOG10` function returns the base-10 logarithm of a specified number. ### Syntax ```dax LOG10(number) ``` #### Parameters

Parameter	Description
number	The positive real number, column or mocker for which you want to calculate the base-10 logarithm.

### Return value The base-10 logarithm of a number. ### Examples

Formula	Description	Result
`LOG10([HEALTHCARE_EXPENSES])`	Returns the base-10 log of `[HEALTHCARE_EXPENSES]` per row.	Varies by row (and errors for non-positive values).
`LOG10(1000)`	Returns the base-10 log of `1000`.	`3`
`LOG10([HEALTHCARE_EXPENSES] + 1)`	Adds `1` first, then computes the base-10 log.	Varies by row.
`IF([HEALTHCARE_EXPENSES] > 0, LOG10([HEALTHCARE_EXPENSES]), 0)`	Computes `LOG10` only for positive values; otherwise returns `0`.	Either `0` or a log value per row.
`ROUND(LOG10([HEALTHCARE_EXPENSES]), 2)`	Computes base-10 log and rounds it to 2 decimals.	Varies by row.

## LOWER The `LOWER` function converts all uppercase letters in a text string to lowercase. ### Syntax ```dax LOWER("text") ``` #### Parameters

Parameter	Description
"text"	The text, column or mocker you want to convert to lowercase. The `LOWER` function does not alter characters in the text that are not letters.

### Return value Text converted to lowercase. ### Examples

Formula	Description	Result
`LOWER([ADDRESS])`	Converts `[ADDRESS]` to lowercase per row.	Varies by row.
`LOWER("John.Doe@Example.com")`	Converts a literal string to lowercase.	`"john.doe@example.com"`
`LOWER(CONCATENATE([FIRSTNAME], ".", [LASTNAME]))`	Builds `"First.Last"` then lowercases it.	Varies by row.
`LOWER(TRIM([EMAIL]))`	Trims whitespace from `[EMAIL]`, then lowercases it.	Varies by row.
`CONCATENATE(LOWER([FIRSTNAME]), ".", LOWER([LASTNAME]), "@", MOCK_FREE_EMAIL_DOMAIN)`	Builds a lowercase email-like string using name columns plus a generated domain.	Varies by row.

## MINUTE The `MINUTE` function returns the minute component of a time value as an integer, ranging from 0 to 59. Time values are a portion of a date value and are represented as a decimal number (e.g., 12:00 PM is represented as `0.5` since it is half of a day). ### Syntax ```dax MINUTE(time) ``` #### Parameters

Parameter	Description
time	The time, column or mocker from which you want to extract the minute. This can be entered as a text string (e.g., `"4:30 PM"`), as a decimal number (e.g., `0.78125` representing 6:45 PM), or as the result of other formulas or functions.

### Return value Minute component of given time. ### Examples

Formula	Description	Result
`MINUTE("4:30:45 PM")`	Extracts the minute component from a literal time string.	`30`
`MINUTE([START])`	Extracts the minute component from the `[START]` time/datetime per row.	Varies by row.
`MINUTE("6/24/2012 07:05:00")`	Extracts the minute component from a literal datetime string.	`5`
`IF(MINUTE([START]) = 0, "on_the_hour", "off_cycle")`	Flags whether `[START]` happens exactly on the hour.	Either `"on_the_hour"` or `"off_cycle"` per row.
`MINUTE([START]) + (HOUR([START]) * 60)`	Converts a time to minutes since midnight.	Varies by row.

## MOD Calculates the remainder of a division and always ensures it matches the sign of the divisor for consistency. * If the divisor is 0, `MOD` returns the `#DIV/0!` error value. * The `MOD` function can be expressed using the `INT` function as follows:\ \&#xNAN;*`MOD(n, d) = n - d*INT(n/d)`* ### Syntax ```dax MOD(dividend, divisor) ``` #### Parameters

Parameter	Description
dividend	The number, column or mocker for which you want to find the remainder.
divisor	The number, column or mocker by which you want to divide the number.

### Return value The remainder when one integer is divided by another. ### Examples The following formula returns the remainders when integers with different signs is divided by integers with different signs.

Formula	Description	Result
`MOD(5, 2)`	Returns the remainder of `5 / 2`.	`1`
`MOD(-5, 2)`	Returns the remainder of `-5 / 2` (same sign as the divisor).	`1`
`MOD(5, -2)`	Returns the remainder of `5 / -2` (same sign as the divisor).	`-1`
`MOD(-5, -2)`	Returns the remainder of `-5 / -2` (same sign as the divisor).	`-1`
`MOD([INVOICE_ID], 10)`	Returns the last digit (mod 10) of `[INVOICE_ID]` per row.	Varies by row.
`IF(MOD([INVOICE_ID], 2) = 0, "even", "odd")`	Classifies invoice ids as even or odd using a mod 2 check.	Either `"even"` or `"odd"` per row.

## MONTH The `MONTH` function returns the month of a date represented by a serial number. The month is provided as an integer ranging from 1 (January) to 12 (December). Values returned by the `YEAR`, `MONTH`, and `DAY` functions are based on the Gregorian calendar, regardless of the display format of the provided date. ### Syntax ```dax MONTH(number) ``` #### Parameters

Parameter	Description
number	The date, column or mocker from which you want to extract the month. Dates should be entered using the `DATE` function or as results from other formulas or functions (e.g., `DATE(2008, 5, 23)` for May 23, 2008). Entering dates as text can cause issues.

### Return value The month for the given date value. ### Examples

Formula	Description	Result
`MONTH([BIRTHDATE])`	Returns the month number from `[BIRTHDATE]` per row.	Varies by row (1–12).
`MONTH(DATE(2024, 12, 31))`	Returns the month number from a constructed date.	`12`
`MONTH("24/8/2012")`	Returns the month number from a literal date string.	`8` (locale dependent).
`MONTH(DATEADD([BIRTHDATE], 6, "month"))`	Adds 6 months to `[BIRTHDATE]` then extracts the month.	Varies by row.
`IF(MONTH([ORDER_DATE]) = 12, "holiday_season", "regular")`	Flags December orders as holiday season.	Either `"holiday_season"` or `"regular"` per row.

## NOT The `NOT` function reverses the logical value of its argument. If the argument is `TRUE`, `NOT` returns `FALSE`, and if the argument is `FALSE`, `NOT` returns `TRUE`. One common use of the `NOT` function is to enhance the functionality of other logical functions, such as `IF`. By using `NOT` within the logical test of an `IF` function, you can test multiple conditions and control the flow of logic based on reversed outcomes. ### Syntax ```dax NOT(Logicalvalue) ``` #### Parameters

Parameter	Description
Logicalvalue	A column or expression that can be evaluated to `TRUE` or `FALSE`.

### Return value Complements (inverts) a logical value. ### Examples

Formula	Description	Result
`IF( AND( NOT([GENDER] = "M"), NOT([HEALTHCARE_EXPENSES] < 100) ), MOCK_NAME_FEMALE, MOCK_NAME_MALE )`	Generates a female name when gender is not `"M"` and expenses are not < 100; otherwise generates a male name.	Varies by row.
`NOT([IS_ACTIVE] = 1)`	Inverts the active flag check.	`TRUE` or `FALSE` per row.
`IF(NOT([ZIP] = "0000XX"), "valid", "fallback")`	Returns `"valid"` unless `[ZIP]` equals the sentinel value `"0000XX"`.	Either `"valid"` or `"fallback"` per row.
`NOT(ISNULL([ZIP]))`	Returns `TRUE` when `[ZIP]` is present (not NULL).	`TRUE` or `FALSE` per row.
`IF(NOT(AND([COUNTRY] = "US", LEN([ZIP]) = 5)), "invalid_zip", "ok")`	Validates US ZIPs: must be length 5 when country is `"US"`.	Either `"invalid_zip"` or `"ok"` per row.

## ODD The `ODD` function rounds a number up to the nearest odd integer. * If the `number` is nonnumeric, the function returns an error. * The function always rounds a value up and away from zero, regardless of its sign. If the `number` is already an odd integer, no rounding occurs. ### Syntax ```dax ODD(number) ``` #### Parameters

Parameter	Description
number	The number, column or mocker to be rounded up to the nearest odd integer.

### Return value A positive number rounded up to the nearest odd integer and a negative number rounded down to the nearest odd integer. ### Examples The following formulas return positive numbers rounded up to the nearest odd integers and negative numbers rounded down to the nearest odd integers.

DAX Formula	Description	Result
`ODD(3.4)`	Rounds 3.4 up to the nearest odd integer.	5
`ODD(3)`	Rounds 3 up to the nearest odd integer.	3
`ODD(6)`	Rounds 6 up to the nearest odd integer.	7
`ODD(-3)`	Rounds -3 up to the nearest odd integer.	-3
`ODD(-4)`	Rounds -4 up (away from 0) to the nearest odd integer.	-5

## OR The `OR` function returns `TRUE` if any of its arguments evaluate to `TRUE`, and returns `FALSE` if all arguments evaluate to `FALSE`. * The arguments must evaluate to logical values, such as `TRUE` or `FALSE`, or be arrays that contain logical values. * If an array or range argument contains text or empty values, those are ignored. * If no logical values are found in the specified range, `OR` returns an error. * You can use an `OR` array formula to check if a value occurs within an array. ### Syntax ```dax OR(Logicalvalue1, Logicalvalue2 ...Logicalvalue30) ``` #### Parameters

Parameter	Description
Logicalvalue1	The first condition to test, which can evaluate to either `TRUE` or `FALSE`.
Logicalvalue2 ...Logicalvalue30	Additional conditions to test, up to a maximum of 30 conditions, each of which can evaluate to either `TRUE` or `FALSE`.

### Return value `TRUE` if at least one argument is `TRUE`. ### Examples

Formula	Description	Result
`OR([HEALTHCARE_COVERAGE] < 100, [HEALTHCARE_EXPENSES] > 250)`	Returns `TRUE` when coverage is < 100 or expenses are > 250.	`TRUE` or `FALSE` per row.
`OR([GENDER] = "F", [GENDER] = "M", [GENDER] = "X")`	Checks whether gender is one of `"F"`, `"M"`, or `"X"`.	`TRUE` or `FALSE` per row.
`IF(OR([STATE] = "Massachusetts", [STATE] = "California"), "US", "non-US")`	Labels rows as `"US"` when state is MA or CA.	Either `"US"` or `"non-US"` per row.
`OR(ISNULL([EMAIL]), ISNULL([PHONE]))`	Returns `TRUE` when either email or phone is missing.	`TRUE` or `FALSE` per row.
`IF(OR([COUNTRY] = "US", [COUNTRY] = "CA"), "NA", "OTHER")`	Classifies countries into North America vs other.	Either `"NA"` or `"OTHER"` per row.

## PI The `PI` function returns the mathematical constant pi (π), accurate to 15 digits, which is approximately 3.14159265358979. ### Syntax ```dax PI() ``` #### Parameters The `PI` function does not require any arguments. It simply returns the value of π. ### Return value Returns 3.14159265358979, the value of the mathematical constant PI to 14 decimal places. ### Examples

Formula	Description	Result
`PI() * (5 ^ 2)`	Computes the area of a circle with radius `5` (πr²).	`~78.5398`
`2 * PI() * 5`	Computes the circumference of a circle with radius `5` (2πr).	`~31.4159`
`PI() * POWER([RADIUS], 2)`	Computes πr² using `[RADIUS]` per row.	Varies by row.
`ROUND(PI(), 4)`	Rounds π to 4 decimals.	`3.1416`
`ROUND(PI() / 180, 8)`	Converts degrees to radians factor (π/180), rounded.	`0.01745329`

## POWER The `POWER` function returns the result of a base number raised to a specified power. You can also use the `^` operator as an alternative to the `POWER` function, such as `4^3`, to indicate that the base number (4) is raised to the power of 3. ### Syntax ```dax POWER(base, exponent) ``` #### Parameters

Parameter	Description
base	The base number, column or mocker, which can be any real number.
exponent	The exponent number, column or mocker to which the base number is raised.

### Return value A number raised to another number. ### Examples

Formula	Description	Result
`POWER(1.1, 3)`	Raises `1.1` to the power of `3`.	`1.331`
`POWER([AMOUNT], [EXPONENT])`	Raises `[AMOUNT]` to `[EXPONENT]` per row.	Varies by row.
`4 ^ 3`	Raises `4` to the power of `3` using the `^` operator.	`64`
`SQRT(POWER([X], 2) + POWER([Y], 2))`	Computes the Euclidean norm (√(x² + y²)) per row.	Varies by row.
`ROUND(POWER(1 + [RATE], [YEARS]), 6)`	Computes compound growth then rounds to 6 decimals.	Varies by row.

## PROPER The `PROPER` function capitalizes the first letter of each word in a text string and any other letters that follow a non-letter character. It converts all other letters to lowercase. ### Syntax ```dataweave PROPER("text") ``` #### Parameters

Parameter	Description
"text"	The text, column or mocker you want to format. This can be a text string enclosed in quotation marks, a formula that returns text, or a reference to a value containing the text you want to capitalize.

### Return value Capitalized words or texts. ### Examples

Formula	Description	Result
`PROPER("5-star 99hoTels AMSTERDAM")`	Capitalizes the first letter of each word and lowercases the rest.	`"5-Star 99hotels Amsterdam"`
`PROPER([ADDRESS])`	Applies proper casing to `[ADDRESS]` per row.	Varies by row.
`PROPER(MOCK_ADDRESS)`	Applies proper casing to a generated address.	Varies by row.
`PROPER(TRIM([ADDRESS]))`	Trims whitespace first, then applies proper casing.	Varies by row.
`PROPER(LOWER([FULL_NAME]))`	Lowercases `[FULL_NAME]` first, then applies proper casing.	Varies by row.

## PV The `PV` function returns the present value of an annuity based on periodic, fixed payments to be made in the future and a fixed interest rate. The present value is the total amount that a series of future payments is worth now. * An annuity is a series of fixed payments made over time, such as a mortgage or a savings plan. * The `rate` and `nper` arguments must be based on the same units of time. For example, if `rate` is calculated using months, `nper` should also be calculated using months. * In financial functions, cash outflows (such as deposits or payments) are represented by negative numbers, while cash inflows (such as dividends) are represented by positive numbers. ### Syntax ```dataweave PV(Rate, Nper, Pmt, Fv, Type) ``` #### Parameters

Parameter	Description
Rate	The interest rate per period. For example, if you have a car loan with an annual percentage rate (APR) of 10% and make monthly payments, the rate per period is `0.1/12`, or `0.0083`.
Nper	The total number of payment periods in the annuity. For example, a two-year car loan with monthly payments has `2 * 12 = 24` payment periods.
Pmt	The payment made each period, which typically includes both principal and interest and remains constant over the life of the annuity.
Fv	The future value or cash balance you want after the final payment. For example, the future value of a loan is $0 after the final payment. If omitted, `0` is assumed.
Type	Specifies when payments are due—`0` for payments at the end of the period, or `1` for payments at the beginning. If omitted, `0` is assumed.

### Return value The present value of an investment. ### Examples

Formula	Description	Result
`PV(0.06/12, 4*12, -250.99)`	Calculates the present value of a 4-year loan at 6% APR with monthly payments.	`~10687`
`PV([RATE], [NPER], [PMT], [FV], [TYPE])`	Calculates present value from per-row rate/period/payment inputs.	Varies by row.
`PV(0.06/12, 4*12, -250.99, 0, 0)`	Same as above, with explicit `FV=0` and end-of-period payments (`Type=0`).	`~10687`
`PV(0.06/12, 4*12, -250.99, 1000, 1)`	Computes present value with a future value of `1000` and beginning-of-period payments.	`~11778`
`PV(0.06/12, 4*12, -250.99, 0, 1)`	Computes present value with beginning-of-period payments (`Type=1`).	`~10740`
`ROUND(PV([RATE], [NPER], [PMT], [FV], [TYPE]), 2)`	Rounds the computed present value to 2 decimals.	Varies by row.

## QUARTER The `QUARTER` function returns the quarter of the year for a given date, represented as a number from 1 to 4, where: * 1 corresponds to January – March, * 2 corresponds to April – June, * 3 corresponds to July – September, * 4 corresponds to October – December. ### Syntax ```dax QUARTER(date) ``` #### Parameters

Parameter	Description
date	The date, column or mocker for which you want to determine the quarter.

### Return value An integer from 1 to 4, representing the quarter of the year. ### Examples

Formula	Description	Result
`QUARTER(DATE(2012, 8, 24))`	Returns the quarter for Aug 24, 2012.	`3`
`QUARTER([BIRTHDATE])`	Returns the quarter for `[BIRTHDATE]` per row.	Varies by row (1–4).
`QUARTER(DATEVALUE("2024/05/01"))`	Parses the date first, then returns its quarter.	`2`
`QUARTER(DATEADD([BIRTHDATE], 3, "month"))`	Adds 3 months to `[BIRTHDATE]` then returns the quarter.	Varies by row.
`IF(QUARTER([ORDER_DATE]) = 4, "peak", "normal")`	Flags Q4 orders as peak season.	Either `"peak"` or `"normal"` per row.

## QUOTIENT The `QUOTIENT` function returns the integer portion of a division, discarding the remainder. This function is useful when you only need the whole number result of a division. ### Syntax ```dax QUOTIENT(dividend, divisor) ``` #### Parameters

Parameter	Description
dividend	The dividend column, mocker or the number to be divided.
divisor	The divisor column, mocker or the number by which the numerator is divided.

### Return value Integer part of a division. ### Examples

Formula	Description	Result
`QUOTIENT(-12.5, 5.3)`	Returns the integer part of the division.	`-2`
`QUOTIENT([REVENUE], [NUMBER_OF_SALES])`	Computes revenue per sale and returns the integer portion.	Varies by row.
`QUOTIENT(17, 3)`	Returns the integer part of `17/3`.	`5`
`QUOTIENT([TOTAL_SECONDS], 60)`	Converts seconds to whole minutes per row.	Varies by row.
`IF(QUOTIENT([ORDER_TOTAL], 1000) >= 1, "high_value", "standard")`	Flags orders of at least 1000 as high value.	Either `"high_value"` or `"standard"` per row.

## RAND The `RAND` function returns a random real number that is evenly distributed between 0 (inclusive) and 1 (exclusive). Each time the worksheet recalculates, a new random number is generated. ### Syntax ```dax RAND() ``` #### Parameters The `RAND` function does not require any arguments. To generate a random real number between two specified values `a` and `b`, use the following formula: ```dax a + (b - a) * RAND() ``` ### Return value A random number between 0 and 1. ### Examples

Formula	Description	Result
`5 + (3 - 5) * RAND()`	Generates a random number in the range `[3, 5)`.	Random number per recalculation.
`RAND()`	Generates a random number in the range `[0, 1)`.	Random number per recalculation.
`100 * RAND()`	Scales a random number to the range `[0, 100)`.	Random number per recalculation.
`ROUND(RAND(), 3)`	Rounds a random number to 3 decimals.	Random number per recalculation.
`IF(RAND() < 0.05, "rare", "common")`	Returns `"rare"` about 5% of the time.	Either `"rare"` or `"common"` per recalculation.

## RANDBETWEEN The `RANDBETWEEN` function returns a random integer between the specified lower and upper bounds. Each time the worksheet recalculates, a new random integer is generated. ### Syntax ```dax RANDBETWEEN(Lowerbound, Upperbound) ``` #### Parameters

Parameter	Description
lowerbound	The smallest integer, column or mocker that can be returned by the function.
upperbound	The largest integer, column or mocker that can be returned by the function.

### Return value A random integer between two numbers. ### Examples

Formula	Description	Result
`RANDBETWEEN(3, 5)`	Returns a random integer between 3 and 5 (inclusive).	`3`, `4`, or `5` per recalculation.
`RANDBETWEEN(-10, 10)`	Returns a random integer between -10 and 10 (inclusive).	An integer in `[-10, 10]` per recalculation.
`RANDBETWEEN([MIN_AGE], [MAX_AGE])`	Returns a random integer between the per-row bounds.	Varies by row and recalculation.
`DATEADD(TODAY(), RANDBETWEEN(-30, 30), "day")`	Returns a random date within ±30 days of today.	Varies by run date and recalculation.
`CHAR(RANDBETWEEN(48, 57))`	Generates a random digit character (`0`–`9`).	A character `"0"`…`"9"` per recalculation.

## REPLACE The `REPLACE` function replaces part of a text string with a different text string, based on the number of characters you specify. ### Syntax ```dax REPLACE(old_text, start_num, num_chars, new_text) ``` #### Parameters

Parameter	Description
old_text	The original text, column or mocker in which you want to replace some characters.
start_num	The position of the character in `old_text` that you want to start replacing with `new_text`.
num_chars	The number of characters in `old_text` that you want `REPLACE` to replace with `new_text`.
new_text	The text, column or mocker that will replace the specified characters or bytes in `old_text`.

### Return value Part of a string with a new string. ### Examples

Formula	Description	Result
`REPLACE("Hello Jupyter!", 6, 7, "Moon")`	Replaces 7 characters starting at position 6 with `"Moon"`.	`"Hello Moon!"`
`REPLACE([PASSPORT], 6, 7, "004A")`	Replaces 7 characters in `[PASSPORT]` starting at position 6.	Varies by row.
`REPLACE([PASSPORT], 1, 2, "NL")`	Overwrites the first two characters of `[PASSPORT]` with `"NL"`.	Varies by row.
`REPLACE(UPPER([COUNTRY_CODE]), 1, 2, "NL")`	Uppercases `[COUNTRY_CODE]` then replaces the first two characters with `"NL"`.	Varies by row.
`REPLACE([PASSPORT], LEN([PASSPORT]) - 1, 2, "XX")`	Replaces the last 2 characters of `[PASSPORT]` with `"XX"`.	Varies by row.

## RIGHT The `RIGHT` function returns a specified number of characters from the right side of a string. ### Syntax ```dax RIGHT("text", number) ``` #### Parameters

Parameter	Description
"text"	The text, column or mocker from which the rightmost characters are returned. If the string contains `Null`, the function returns `Null`.
number	A numeric expression indicating how many characters to return. If `0`, a zero-length string (`""`) is returned. If the length is greater than or equal to the number of characters in the string, the entire string is returned.

### Return value The input text starting from a specified character index. ### Examples

Formula	Description	Result
`RIGHT("Hello Earth!", 6)`	Returns the last 6 characters of a literal string.	`"Earth!"`
`RIGHT([STATE], MOCK_RANDOM_DIGIT)`	Returns a random-length suffix of `[STATE]` per row.	Varies by row.
`RIGHT("Massachusetts", 4)`	Returns the last 4 characters of a literal string.	`"etts"`
`RIGHT(CONCATENATE("00000", [ZIP]), 5)`	Left-pads ZIP with zeros then returns the last 5 characters.	Varies by row (e.g., `"02134"`).
`RIGHT([PHONE], 4)`	Returns the last 4 characters of a phone number per row.	Varies by row.

## ROUND The `ROUND` function rounds a number to a specified number of digits. * If `Count` is greater than 0, the `Number` is rounded to the specified number of decimal places. * If `Count` is 0, the `Number` is rounded to the nearest integer. * If `Count` is less than 0, the `Number` is rounded to the left of the decimal point. * To always round up (away from zero), use the `ROUNDUP` function. * To always round down (toward zero), use the `ROUNDDOWN` function. ### Syntax ```dax ROUND(number, count) ``` #### Parameters

Parameter	Description
number	The number, column or mocker you want to round.
count	The number of digits to which you want to round the `number`.

### Return value A number that is rounded to a certain number of decimal places. ### Examples The following formulas round the numbers to various decimal places.

Formula	Description	Result
`ROUND(3.25, 1)`	Rounds 3.25 to one decimal place	3.2
`ROUND(5.149, 1)`	Rounds 5.149 to one decimal place	5.1
`ROUND(-6.745, 2)`	Rounds -6.745 to two decimal places	-6.75
`ROUND(23.9, -1)`	Rounds 23.9 to one decimal place to the left of the decimal point	20
`ROUND(575.9, -3)`	Rounds 575.9 to the nearest multiple of 1000	1000
`ROUND(2.33, -1)`	Rounds 2.33 to the nearest multiple of 10	0
`ROUND(-51.52, -2)`	Rounds -51.52 to the nearest multiple of 100	-100

## ROUNDDOWN The `ROUNDDOWN` function rounds a number down, toward zero. * `ROUNDDOWN` behaves similarly to `ROUND`, except that it always rounds a number down. * If `Count` is greater than 0, the `number` is rounded down to the specified number of decimal places. * If `Count` is 0, the `number` is rounded down to the nearest integer. * If `Count` is less than 0, the `number` is rounded down to the left of the decimal point. ### Syntax ```dax ROUNDDOWN(number, count) ``` #### Parameters

Parameter	Description
number	Any real number, column or mocker that you want to round down.
count	The number of digits to which you want to round the `number`.

### Return value A number rounded down, toward zero, to a certain precision. ### Examples The following formulas round down the numbers to various decimal places.

Formula	Description	Result
`ROUNDDOWN(5.3, 0)`	Rounds 5.3 down to zero decimal place	5
`ROUNDDOWN(66.8,0)`	Rounds 66.8 down to zero decimal places	66
`ROUNDDOWN(1.24279, 3)`	Rounds 1.24279 down to three decimal places	1.242
`ROUNDDOWN(-1.24279, 1)`	Rounds -1.24279 down to one decimal places	-1.2
`ROUNDDOWN(12427.98637, -2)`	Rounds 12427.98637 down to 2 decimal places to the left of the decimal point.	12400

## ROUNDUP The `ROUNDUP` function rounds a number up, away from 0 (zero). * `ROUNDUP` behaves similarly to `ROUND`, except that it always rounds a number up. * If `Count` is greater than 0, the `Number` is rounded up to the specified number of decimal places. * If `Count` is 0, the `Number` is rounded up to the nearest integer. * If `Count` is less than 0, the `Number` is rounded up to the left of the decimal point. ### Syntax ```dax ROUNDUP(number, count) ``` #### Parameters

Parameter	Description
number	Any real number, column or mocker that you want rounded up.
count	The number of digits to which you want to round the `number`.

### Return value A number rounded up, away from zero, to a certain precision. ### Examples The following formulas round up the numbers to various decimal places.

Formula	Description	Result
`ROUNDUP(5.3, 0)`	Rounds 5.3 up to zero decimal place	6
`ROUNDUP(66.8,0)`	Rounds 66.8 up to zero decimal place	67
`ROUNDUP(1.24279, 3)`	Rounds 1.24279 up to three decimal places	1.243
`ROUNDUP(-1.24279, 1)`	Rounds -1.24279 up to one decimal places	-1.3
`ROUNDUP(12427.98637, -2)`	Rounds 12427.98637 up to 2 decimal places to the left of the decimal point.	12500

## SECOND The `SECOND` function returns the seconds component of a time value, as an integer ranging from 0 to 59. ### Syntax ```dax SECOND(time) ``` #### Parameters

Parameter	Description
time	The time, column or mocker values from which you want to extract the seconds. This can be entered as a text string (e.g., `"6:45 PM"`), as a decimal number (e.g., `0.78125` representing 6:45 PM), or as the result of other formulas or functions (e.g., `TIMEVALUE("6:45 PM")`).

### Return value Second component of given time. ### Examples

Formula	Description	Result
`SECOND("2:25:55 PM")`	Extracts the seconds component from a literal time string.	`55`
`SECOND([START_TIME])`	Extracts the seconds component from `[START_TIME]` per row.	Varies by row (0–59).
`SECOND("6/24/2012 07:30:05")`	Extracts the seconds component from a literal datetime string.	`5`
`IF(SECOND([START_TIME]) = 0, "exact_minute", "has_seconds")`	Flags whether the time is exactly on a minute boundary.	Either `"exact_minute"` or `"has_seconds"` per row.
`SECOND([START_TIME]) + (MINUTE([START_TIME]) * 60)`	Converts the seconds within the hour (minute*60 + second).	Varies by row (0–3599).

## SQRT The `SQRT` function returns the positive square root of a given number. ### Syntax ```dax SQRT(number) ``` #### Parameters

Parameter	Description
number	The number, column or mocker for which you want to find the square root. If the `number` is negative, `SQRT` returns an error value.

### Return value The positive square root of a number. ### Examples

Formula	Description	Result
`SQRT(49)`	Returns the square root of a literal number.	`7`
`SQRT([PAYER_COVERAGE])`	Returns the square root of `[PAYER_COVERAGE]` per row.	Varies by row (and errors for negative values).
`SQRT(POWER([PAYER_COVERAGE], 2))`	Squares the value then takes the square root (returns absolute value for non-negative inputs).	Varies by row.
`ROUND(SQRT([PAYER_COVERAGE]), 2)`	Computes the square root then rounds to 2 decimals.	Varies by row.
`IF([PAYER_COVERAGE] < 0, 0, SQRT([PAYER_COVERAGE]))`	Guards against negative inputs by returning `0` instead.	Either `0` or a square-root value per row.

## SUBSTITUTE The `SUBSTITUTE` function replaces specific text within a text string with new text. Use `SUBSTITUTE` when you want to replace specific occurrences of text in a string; use `REPLACE` when you want to replace text based on its position in the string. ### Syntax ```dax SUBSTITUTE(text, old_text, new_text, occurrence) ``` #### Parameters

Parameter	Description
text	The text string, column or mocker containing the text where you want to substitute characters.
old_text	The text you want to replace.
new_text	The text that will replace `Old_text`
occurrence	Specifies which occurrence of `Old_text` you want to replace with `New_text`. If you provide `Occurrence`, only that specific instance of `Old_text` is replaced. If omitted, all occurrences of `Old_text` in the text are replaced with `New_text`.

### Return value String where occurrences of `Old_text` are replaced by `New_text`. It replaces only specific occurrence if last parameter is provided. ### Examples

Formula	Description	Result
`SUBSTITUTE("April 2, 2012", "2", "3", 3)`	Replaces the 3rd occurrence of `"2"` with `"3"`.	`"April 2, 2013"`
`SUBSTITUTE([PASSPORT], "X", "S", 1)`	Replaces the first `"X"` in `[PASSPORT]` with `"S"` per row.	Varies by row.
`SUBSTITUTE("john.doe@example.com", ".", "_")`	Replaces all dots with underscores.	`"john_doe@example_com"`
`SUBSTITUTE(LOWER([EMAIL]), " ", "")`	Lowercases `[EMAIL]` then removes all spaces.	Varies by row.
`SUBSTITUTE([ADDRESS], CHAR(10), " ")`	Replaces line breaks with spaces.	Varies by row.

## SWITCH The `SWITCH` function evaluates one value (referred to as the expression) against a list of values and returns the result corresponding to the first matching value. If no match is found, an optional default value may be returned. ### Syntax ```dataweave SWITCH(expression1, value1, result2, value2..., result_n, default) ``` #### Parameters

Parameter	Description
expression1	The value (such as a number, date, text or column) that will be compared against `value_1` through `value_N`.
value1, value2, ... value_N	The columns to be compared against `Expression1` through `expression_n`.
result1…result_n	The values to be returned when the corresponding `value1` through `value_N` arguments match result`1` through `result_n`. Each `result_n` must be provided for each corresponding `value_N` argument.
default	The column to return if no matches are found in the `value_N` expressions. The `Default` argument is identified by having no corresponding `result_n` expression. It must be the final argument in the function.

### Return value A list of arguments, consisting of an expression followed by a value. ### Examples

Formula	Description	Result
`SWITCH(3, 1, "Dog", 2, "Cat", 3, "Horse", "?")`	Maps the expression `3` to the matching value.	`"Horse"`
`SWITCH([STATUS], 1, "New", 2, "Active", 3, "Closed", "Unknown")`	Maps numeric status codes to labels, with `"Unknown"` as default.	Varies by row.
`SWITCH([GENDER], "F", "female", "M", "male", "other")`	Maps gender codes to normalized values.	Either `"female"`, `"male"`, or `"other"` per row.
`SWITCH(TRUE(), [SCORE] >= 90, "A", [SCORE] >= 80, "B", [SCORE] >= 70, "C", "D")`	Implements a tiered grade mapping using `TRUE()` as the expression.	`"A"`, `"B"`, `"C"`, or `"D"` per row.
`SWITCH(LEFT([COUNTRY], 2), "US", "United States", "NL", "Netherlands", "Other")`	Maps country prefixes to full names.	Either `"United States"`, `"Netherlands"`, or `"Other"` per row.

## TODAY The `TODAY` function returns the current date. ### Syntax ```dax TODAY() ``` #### Parameters The `TODAY` function has no arguments. ### Return value An integer representing the current date as the number of full days since nullDate. ### Examples

Formula	Description	Result
`YEAR(TODAY()) - 1998`	Approximates age in years for a person born in 1998 (based on the current year).	Varies by run date.
`TODAY()`	Returns the current date.	Varies by run date.
`DATEDIFF([BIRTHDATE], TODAY(), "year")`	Computes age in full years based on `[BIRTHDATE]`.	Varies by row and run date.
`DATEADD(TODAY(), 30, "day")`	Returns the date 30 days from today.	Varies by run date.
`IF(TODAY() > DATE(2025, 1, 1), "after_2025", "before_2025")`	Labels whether today is after Jan 1, 2025.	Either `"after_2025"` or `"before_2025"` (run date dependent).

## TRIM The The **TRIM** function removes all unnecessary whitespace from a text string, ensuring cleaner and standardized text formatting. It is commonly used to clean imported or user-entered data that may contain irregular spacing. ### Syntax ```dax TRIM("text") ``` #### Parameters

Parameter

Description

"text"

The text, column or mocker from which you want to remove extra spaces.

Removes all leading and trailing spaces around a text string.

### Return value A cleaned text string with: * No leading or trailing whitespace. ### Examples

Formula	Description	Result
`TRIM(" Senatus Populusque Romanus ")`	Removes leading and trailing spaces from a literal string.	`"Senatus Populusque Romanus"`
`TRIM([ADDRESS])`	Removes leading and trailing spaces from `[ADDRESS]` per row.	Varies by row (e.g., `" 123 Main St "` → `"123 Main St"`).
`TRIM(MOCK_ADDRESS)`	Trims whitespace from a generated address.	Varies by row.
`TRIM(CONCATENATE([FIRSTNAME], " ", [LASTNAME]))`	Concatenates first and last name and trims the result.	Varies by row.
`LOWER(TRIM([EMAIL]))`	Trims `[EMAIL]` first, then lowercases it.	Varies by row.

## TRUE The `TRUE` function returns the logical value `TRUE`. Use this function to return `TRUE` based on a condition. For example: * `IF(2=2,TRUE(), FALSE())` returns `TRUE` if the value 2 is equal to 2. You can also enter the value `TRUE` directly into formulas without using the function. For example: * `IF(2=2,TRUE, FALSE)` also returns `TRUE` if the condition is met. If the condition is not met, both examples return `FALSE`. ### Syntax ```dax TRUE() ``` #### Parameters The `TRUE` function does not require any arguments. ### Return value The logical value that is set to TRUE. ### Examples

Formula	Description	Result
`IF(2 = 2, TRUE, FALSE)`	Returns `TRUE` because the condition is always true.	`TRUE`
`TRUE()`	Returns the logical value `TRUE`.	`TRUE`
`IF([IS_ACTIVE] = 1, TRUE(), FALSE())`	Returns `TRUE` when `[IS_ACTIVE]` equals `1`; otherwise `FALSE`.	`TRUE` or `FALSE` per row.
`AND(TRUE(), [IS_ACTIVE] = 1)`	Shows that `AND` with `TRUE()` behaves like the other condition.	`TRUE` or `FALSE` per row.
`IF(TRUE(), MOCK_FIRST_NAME, MOCK_LAST_NAME)`	Always returns the `then` branch because the condition is always true.	Varies by row.

## UPPER The `UPPER` function converts text to uppercase. ### Syntax ```dax UPPER("text") ``` #### Parameters

Parameter	Description
"text"	The text, column or mocker you want to convert to uppercase. This can be a reference or a text string.

### Return value The text that is converted to uppercase. ### Examples

Formula	Description	Result
`UPPER("hello world!")`	Converts a literal string to uppercase.	`"HELLO WORLD!"`
`UPPER([ADDRESS])`	Converts `[ADDRESS]` to uppercase per row.	Varies by row.
`UPPER(MOCK_COMPANY_EMAIL)`	Converts a generated company email to uppercase.	Varies by row.
`UPPER(LOWER([EMAIL]))`	Normalizes `[EMAIL]` by lowercasing first, then uppercasing.	Varies by row.
`CONCATENATE(UPPER([STATE]), "-", RIGHT(CONCATENATE("00000", [ZIP]), 5))`	Builds `"STATE-#####"` from state and a left-padded ZIP.	Varies by row (e.g., `"MASSACHUSETTS-02134"`).

## VAR The `VAR` function calculates the variance based on a sample of a population. * `VAR` assumes that its arguments represent a sample of the population. If your data represents the entire population, use the `VARP` function instead. * Arguments can be numbers, names, arrays, or values that contain numbers. * Logical values and text representations of numbers entered directly into the argument list are included in the calculation. * If an argument is an array, only the numbers within that array are considered; empty values, logical values, text, or error values in the array are ignored. * Arguments that contain error values or text that cannot be converted into numbers will cause the function to return an error. * If you want to include logical values and text representations of numbers in the calculation, use the `VARA` function. ### Syntax ```dataweave VAR(value1, value2, ... value30) ``` #### Parameters

Parameter	Description
value1	The first number, column or mocker corresponding to a sample of a population.
value2, ... value30	Additional arguments corresponding to a sample of a population, up to 30 total arguments.

### Return value Variance of a sample. ### Examples

Formula	Description	Result
`VAR(10, 22, 45, 65, 6, 63, 23, 89, 46, 54)`	Computes the sample variance of the provided literal values.	`~714.23`
`VAR([AMOUNT_COVERED], [AMOUNT_UNCOVERED], [COVERED_ENCOUNTERS], [COVERED_PROCEDURES])`	Computes the sample variance across multiple numeric columns for each row.	Varies by row.
`VAR([AMOUNT_COVERED], 0, 100, 250)`	Computes the sample variance of a row value combined with constants.	Varies by row.
`ROUND(VAR([AMOUNT_COVERED], [AMOUNT_UNCOVERED], [COPAY_AMOUNT]), 2)`	Computes sample variance across three columns, then rounds to 2 decimals.	Varies by row.
`SQRT(VAR(10, 20, 30, 40, 50))`	Computes the square root of the sample variance (i.e., sample standard deviation) of the provided values.	`~15.811`

## VAR.P The `VAR.P` function calculates the variance based on the entire population, ignoring logical values and text. * `VAR.P` assumes that its arguments represent the entire population. If your data represents only a sample of the population, use the `VAR.S` function instead. * Arguments can include numbers, names, arrays, or values that contain numbers. * Logical values and text representations of numbers entered directly into the argument list are included in the calculation. * If an argument is an array, only the numbers within that array are considered; empty values, logical values, text, or error values are ignored. * Arguments that contain error values or text that cannot be converted into numbers will cause the function to return an error. * If you want to include logical values and text representations of numbers in the calculation, use the `VARPA` function. ### Syntax ```dax VAR.P(value1, value2, ... value30) ``` #### Parameters

Parameter	Description
value1	The first number, column or mocker corresponding to the population.
value1, value2, ... value30	Additional arguments corresponding to the population, up to 30 total arguments.

### Return value Variance of a population. ### Examples

Formula	Description	Result
`VAR.P(10, 22, 45, 65, 6, 63, 23, 89, 46, 54)`	Computes the population variance of the provided literal values.	`~642.81`
`VAR.P([AMOUNT_COVERED], [AMOUNT_UNCOVERED], [COVERED_ENCOUNTERS], [COVERED_PROCEDURES])`	Computes the population variance across multiple numeric columns for each row.	Varies by row.
`VAR.P([AMOUNT_COVERED], [AMOUNT_UNCOVERED])`	Computes the population variance across two columns for each row.	Varies by row.
`VAR.P([AMOUNT_COVERED], [AMOUNT_UNCOVERED], [COPAY_AMOUNT])`	Computes the population variance across three columns for each row.	Varies by row.
`ROUND(VAR.P(10, 22, 45, 65, 6), 2)`	Computes population variance, then rounds to 2 decimals.	`497.84`

## VAR.S The `VAR.S` function estimates variance based on a sample, ignoring logical values and text. * `VAR.S` assumes that its arguments represent a sample of the population. If your data represents the entire population, use the `VAR.P` function instead. * Arguments can include numbers, names, arrays, or values that contain numbers. * Logical values and text representations of numbers entered directly into the argument list are included in the calculation. * If an argument is an array, only the numbers within that array are considered; empty values, logical values, text, or error values are ignored. * Arguments that contain error values or text that cannot be converted into numbers will cause the function to return an error. * If you want to include logical values and text representations of numbers in the calculation, use the `VARA` function. ### Syntax ```dax VAR.S(value1, value2, ... value30) ``` #### Parameters

Parameter	Description
value1	The first number, column or mocker corresponding to a sample of the population.
value2, ... value30	Additional arguments corresponding to a sample of the population, up to 30 total arguments.

### Return value Variance of a sample. ### Examples

Formula	Description	Result
`VAR.S(10, 22, 45, 65, 6, 63, 23, 89, 46, 54)`	Computes the sample variance of the provided literal values.	`~714.23`
`VAR.S([AMOUNT_COVERED], [AMOUNT_UNCOVERED], [COVERED_ENCOUNTERS], [COVERED_PROCEDURES])`	Computes the sample variance across multiple numeric columns for each row.	Varies by row.
`VAR.S([HEALTHCARE_EXPENSES], [HEALTHCARE_COVERAGE], [COPAY_AMOUNT])`	Computes the sample variance across three columns for each row.	Varies by row.
`VAR.S([AMOUNT_COVERED], [AMOUNT_UNCOVERED], [COPAY_AMOUNT], 0)`	Computes the sample variance across three columns plus a constant `0` for each row.	Varies by row.
`ROUND(VAR.S(10, 22, 45, 65, 6), 2)`	Computes sample variance, then rounds to 2 decimals.	`622.30`

## VARA The `VARA` function estimates variance based on a sample, considering numbers, text representations of numbers, and logical values. * `VARA` assumes that its arguments represent a sample of the population. If your data represents the entire population, use the `VARPA` function instead. * Arguments can include numbers, names, arrays, text representations of numbers, or logical values such as `TRUE` and `FALSE`. * Logical values and text representations of numbers entered directly into the argument list are included in the calculation. * Arguments containing `TRUE` evaluate as 1. * Arguments containing text or `FALSE` evaluate as 0. * If an argument is an array, only the values within that array are considered. Empty values and text within the array are ignored. * Arguments that contain error values or text that cannot be converted into numbers will cause the function to return an error. * If you do not want to include logical values and text representations of numbers in the calculation, use the `VAR` function. ### Syntax ```dataweave VARA(value1, value2, ... value30) ``` #### Parameters

Parameter	Description
value1, value2, ... value30	`value1` is required, and subsequent values are optional. You can provide 1 to 30 values corresponding to a sample of the population.

### Return value Variance of a sample. ### Examples

Formula	Description	Result
`VARA(10, 22, 45, 65, 6, 63, 23, 89, 46, 54)`	Computes sample variance, including any logical/text values (none in this example).	`~714.23`
`VARA([AMOUNT_COVERED], [AMOUNT_UNCOVERED], [COVERED_ENCOUNTERS], [COVERED_PROCEDURES])`	Computes sample variance across multiple columns for each row, including any logical/text values.	Varies by row.
`VARA(TRUE, FALSE, 10, "7")`	Computes sample variance including booleans and text numbers (`TRUE=1`, `FALSE=0`, `"7"=7`).	`23`
`VARA(TRUE, FALSE, [FLAG_A], [FLAG_B], [FLAG_C])`	Computes sample variance including booleans and per-row flag columns.	Varies by row.
`ROUND(VARA(10, 20, 30, TRUE, "7"), 2)`	Computes `VARA` including booleans/text, then rounds to 2 decimals.	`131.30`

## VARP The `VARP` function calculates the variance based on the entire population. * `VARP` assumes that its arguments represent the entire population. If your data represents a sample of the population, use the `VAR` function instead. * Arguments can include numbers, names, arrays, or values that contain numbers. * Logical values and text representations of numbers entered directly into the argument list are included in the calculation. * If an argument is an array, only the numbers within that array are considered; empty values, logical values, text, or error values in the array are ignored. * Arguments that contain error values or text that cannot be converted into numbers will cause the function to return an error. * If you want to include logical values and text representations of numbers in the calculation, use the `VARPA` function. ### Syntax ```dataweave VARP(value1, value2, ... value30) ``` #### Parameters

Parameter	Description
value1	The first number, column or mocker corresponding to the population.
value2, ... value30	Additional arguments corresponding to the population, up to 30 total arguments.

### Return value Variance of a population. ### Examples

Formula	Description	Result
`VARP(10, 22, 45, 65, 6, 63, 23, 89, 46, 54)`	Computes the population variance of the provided literal values.	`~642.81`
`VARP([AMOUNT_COVERED], [AMOUNT_UNCOVERED], [COVERED_ENCOUNTERS], [COVERED_PROCEDURES])`	Computes the population variance across multiple numeric columns for each row.	Varies by row.
`VARP([AMOUNT_COVERED], [AMOUNT_UNCOVERED])`	Computes the population variance across two columns for each row.	Varies by row.
`VARP([AMOUNT_COVERED], [AMOUNT_UNCOVERED], [COPAY_AMOUNT])`	Computes the population variance across three columns for each row.	Varies by row.
`ROUND(VARP(10, 22, 45, 65, 6), 2)`	Computes population variance, then rounds to 2 decimals.	`497.84`

## WEEKNUM The `WEEKNUM` function returns the week number of a specific date. For example, the week containing January 1 is considered the first week of the year and is numbered as week 1. ### Syntax ```dax WEEKNUM(date, [return_type]) ``` #### Parameters

Parameter	Description
date	A date, column or mocker within the week you want to identify. Dates should be entered using the `DATE` function or as the result of other formulas or functions. Entering dates as text may cause errors.
[return_type]	A number that determines which day of the week the week starts on. The default value is 1 (where Sunday is the first day of the week).

### Return value The week number in the year. ### Examples

Formula	Description	Result
`WEEKNUM("24/8/2012")`	Returns the week number for a literal date string.	A week number (locale dependent).
`WEEKNUM([BIRTHDATE])`	Returns the week number for `[BIRTHDATE]` per row.	Varies by row.
`WEEKNUM(DATE(2024, 1, 1), 2)`	Returns the week number for Jan 1, 2024 with Monday as the week start (`return_type=2`).	`1` (depending on week-numbering rules).
`WEEKNUM(TODAY())`	Returns the current week number.	Varies by run date.
`WEEKNUM(DATEADD([ORDER_DATE], 7, "day"), 2)`	Shifts `[ORDER_DATE]` by 7 days, then returns week number with Monday as week start.	Varies by row.

## XOR The `XOR` function returns a logical Exclusive Or (XOR) of all the provided arguments. * The arguments must evaluate to logical values such as `TRUE` or `FALSE`, or be in arrays or references that contain logical values. * If an array or reference argument contains text or empty cells, those values are ignored. * If the specified range contains no logical values, `XOR` returns the `#VALUE!` error. * You can use an XOR array formula to check if a value occurs in an array. * The result of `XOR` is `TRUE` when the number of `TRUE` inputs is odd and `FALSE` when the number of `TRUE` inputs is even. ### Syntax ```dataweave XOR(Logicalvalue1, Logicalvalue2 ... Logicalvalue30) ``` #### Parameters

Parameter	Description
Logicalvalue1, Logicalvalue2 ... Logicalvalue30	`Logical1` is required, and subsequent logical values are optional. You can provide 1 to 30 conditions to test, which can be either `TRUE` or `FALSE`, and can include logical values, arrays, or references.

### Return value TRUE if an odd number of arguments evaluates to TRUE. ### Examples

Formula	Description	Result
`XOR([GENDER] = "M", [HEALTHCARE_COVERAGE] < 250, [HEALTHCARE_EXPENSES] > 1000)`	Returns `TRUE` when an odd number of conditions are true.	`TRUE` or `FALSE` per row.
`XOR(TRUE, FALSE)`	Computes XOR for two literal booleans.	`TRUE`
`XOR([FLAG_A] = 1, [FLAG_B] = 1, [FLAG_C] = 1, [FLAG_D] = 1)`	Returns `TRUE` when 1 or 3 of the flags are true.	`TRUE` or `FALSE` per row.
`IF(XOR([FLAG_A] = 1, [FLAG_B] = 1), "exactly_one", "zero_or_two")`	Labels rows where exactly one of two flags is set.	Either `"exactly_one"` or `"zero_or_two"` per row.
`XOR(AND([A] = 1, [B] = 1), OR([C] = 1, [D] = 1))`	Computes XOR of two composite logical expressions.	`TRUE` or `FALSE` per row.

## YEAR The `YEAR` function returns the year corresponding to a given date as an integer between 1900 and 9999. The values returned by the `YEAR`, `MONTH`, and `DAY` functions will be Gregorian values, regardless of the display format for the supplied date. ### Syntax ```dax YEAR(number) ``` #### Parameters

Parameter	Description
number	The date, column or mocker for which you want to extract the year. Dates should be entered using the `DATE` function or as the result of other formulas or functions. Entering dates as text may cause errors.

### Return value The year as a number according to the internal calculation rules. ### Examples

Formula	Description	Result
`YEAR("24/8/2012")`	Extracts the year from a literal date string.	`2012` (locale dependent).
`YEAR([BIRTHDATE])`	Extracts the year from `[BIRTHDATE]` per row.	Varies by row.
`YEAR(DATE(2024, 12, 31))`	Extracts the year from a constructed date.	`2024`
`YEAR(TODAY())`	Extracts the current year.	Varies by run date.
`IF(YEAR([ORDER_DATE]) < 2000, "legacy", "modern")`	Labels orders before 2000 as legacy.	Either `"legacy"` or `"modern"` per row.

{% hint style="info" %} **Info:** The below functions are planned to be introduced in the future releases of Syntho. {% endhint %} ### Planned Functions for future releases | Function | Description | Format | | ---------- | ------------------------------------------------------------------------------------------------------------------------------- | -------------------------------------- | | STDEV | Returns standard deviation of a sample. When used with column names, the value is calculated anew for each row. | STDEV(Value1, Value2, ... Value30) | | STDEV.P | Returns standard deviation of a population. When used with column names, the value is calculated anew for each row. | STDEV.P(Value1, Value2, ... Value30) | | STDEV.S | Returns standard deviation of a sample. When used with column names, the value is calculated anew for each row. | STDEV.S(Value1, Value2, ... Value30) | | STDEVA | Returns standard deviation of a sample. When used with column names, the value is calculated anew for each row. | STDEVA(Value1, Value2, ... Value30) | | STDEVP | Returns standard deviation of a population. When used with column names, the value is calculated anew for each row. | STDEVP(Value1, Value2, ... Value30) | | STDEVPA | Returns standard deviation of a population. When used with column names, the value is calculated anew for each row. | STDEVPA(Value1, Value2, ... Value30) | | MAX | Returns the maximum value in a list of arguments. When used with column names, the value is calculated anew for each row. | MAX(Number1, Number2, ... Number30) | | MAXA | Returns the maximum value in a list of arguments. When used with column names, the value is calculated anew for each row. | MAXA(Value1, Value2, ... Value30) | | MEDIAN | Returns the median of a set of numbers. When used with column names, the value is calculated anew for each row. | MEDIAN(Number1, Number2, ... Number30) | | MIN | Returns the minimum value in a list of arguments. When used with column names, the value is calculated anew for each row. | MIN(Number1, Number2, ... Number30) | | MINA | Returns the minimum value in a list of arguments. When used with column names, the value is calculated anew for each row. | MINA(Value1, Value2, ... Value30) | | COUNT | Counts how many numbers are in the list of arguments. | COUNT(Value1, Value2, ... Value30) | | COUNTA | Counts how many values are in the list of arguments. | COUNTA(Value1, Value2, ... Value30) | | COUNTBLANK | Returns the number of empty cells. | COUNTBLANK(Range) | | IFNA | Returns the value if the cell does not contain the #N/A (value not available) error value, or the alternative value if it does. | IFNA(Value, Alternate\_value) |