Measures

A measure is a named calculation, such as total sales or average price. It is computed again for each visual, each bar and each row, using the filters that apply there. Measures live in a data model, so every report on that data model can use them.

Before you start

You need a data model with at least one table. See Data models.
To create or edit measures, you need Build permission on the data model. Without it you can only view them.

Create a measure

Open the data model.
Click the Measures tab.
Click New measure.
Enter a Name (up to 128 characters).
Pick the Home table. The measure is listed under this table in reports.
Type the Expression. Example: SUM(Orders[Amount]).
Optional: fill in Display folder, Number format and Description.
Optional: tick Hide from report viewers.
Click Check (or press Ctrl+Enter, or Cmd+Enter on a Mac).
Click Save.

Save the draft and publish the data model so that reports use the new measure. See Data models.

Check as you type

Fresco BI checks the expression a moment after you stop typing:

Valid, followed by the result type.
Or the number of errors, with the line and column of each. The error is underlined in the expression.

Number format

Number format controls how the value is shown. Use:

0 for a digit that is always shown,
# for a digit that is shown only when needed,
, for thousands separators,
. for decimals,
% to show a ratio as a percent (0.41 shows as 41%),
any other text before or after the number, for example a currency sign.

Format	1234.5 shows as
`#,##0`	1,235
`#,##0.00`	1,234.50
`$#,##0`	$1,235

Format	0.415 shows as
`0.0%`	41.5%

The thousands and decimal separators follow the display language.

Find and edit measures

The Measures tab lists every measure with its Name, Formula, Display folder, Number format and Home table.

Type in the search box to find a measure.
Use Display folder to filter by folder.
Click Edit to change a measure, or Delete to remove it.

Tags in the list:

Hidden: the measure is hidden from report viewers.
Needs rewrite: the measure was migrated from another BI tool and not translated yet. It returns blank. The original expression is in its description.

Write an expression

Names

What	How to write it
A column	`Orders[Amount]`
A column of a table with spaces in its name	`'Order lines'[Quantity]`
Another measure	`[Total sales]`
Text	`"North"`
Numbers	`100`, `0.5`

A column must be inside an aggregate function, such as SUM(Orders[Amount]). A measure can use other measures, but measures cannot refer to each other in a loop.

Operators

Operator	Meaning
`+ - * /`	Arithmetic
`=` or `==`, `<>`, `<`, `<=`, `>`, `>=`	Comparison
`IN { ... }`	The value is in a list, for example `Orders[Region] IN { "North", "South" }`. Up to 1,000 values.
`&&`	And
`\|\|`	Or

Variables

Use VAR to name a part of the expression, then RETURN the result:

VAR Sales = SUM(Orders[Amount])
VAR Cost = SUM(Orders[Cost])
RETURN DIVIDE(Sales - Cost, Sales)

Comments

Add comments with //, -- or /* ... */.

Examples

SUM(Orders[Amount])

COUNTROWS(Orders)

DIVIDE([Total sales], SUM(Orders[Quantity]))

CALCULATE([Total sales], Orders[Region] = "North")

CALCULATE([Total sales], ALL(Customers))

SUMX(Orders, Orders[Quantity] * RELATED(Products[Price]))

Functions

These are all the functions you can use in a measure.

Aggregates

Function	What it does
`SUM(column)`	Adds up the numbers.
`AVERAGE(column)`	Average of the numbers.
`MIN(column)`	Smallest number or date.
`MAX(column)`	Largest number or date.
`COUNT(column)`	Number of values that are not blank.
`COUNTROWS(table)`	Number of rows.
`DISTINCTCOUNT(column)`	Number of different values, blank included.
`DISTINCTCOUNTNOBLANK(column)`	Number of different values, blank not included.

Row by row

These functions go through a table row by row, compute the expression for each row, then combine the results.

Function	What it does
`SUMX(table, expression)`	Sum of the expression over the rows.
`AVERAGEX(table, expression)`	Average of the expression over the rows.
`MINX(table, expression)`	Smallest result.
`MAXX(table, expression)`	Largest result.
`COUNTX(table, expression)`	Number of rows where the result is not blank.
`RELATED(column)`	The value from the related table on the one side. Use it inside `SUMX` and the other X functions.

Filters

Function	What it does
`CALCULATE(expression, filter, ...)`	Computes the expression with the given filters. A filter on a column replaces the existing filter on that column. Needs at least one filter.
`FILTER(table, condition)`	The rows that meet a condition on one of the table’s columns. Use it inside `CALCULATE`.
`ALL(table or column, ...)`	Removes the filters on a table or on columns. Use it inside `CALCULATE`.
`REMOVEFILTERS(table or column, ...)`	Same as `ALL`. Use it inside `CALCULATE`.
`ALLEXCEPT(table, column, ...)`	Removes the filters on a table, except on the listed columns. Use it inside `CALCULATE`.
`KEEPFILTERS(filter)`	Keeps the existing filters on the column instead of replacing them. Use it inside `CALCULATE`.
`VALUES(column)`	The values of a column. Use it inside `FILTER`.
`DISTINCT(column)`	The different values of a column. Use it inside `FILTER`.
`HASONEVALUE(column)`	True when the column has exactly one value in the current filters.
`SELECTEDVALUE(column[, alternate])`	The value when there is exactly one, otherwise blank or the alternate value.

Logic

Function	What it does
`IF(condition, then[, else])`	One value or another, by condition.
`SWITCH(expression, value, result, ...[, else])`	The result of the first matching value.
`AND(logical1, logical2)`	True when both are true. Takes exactly two values; use `&&` for more.
`OR(logical1, logical2)`	True when either is true. Takes exactly two values; use `\|\|` for more.
`NOT(logical)`	Turns true into false and false into true.
`ISBLANK(value)`	True when the value is blank.
`COALESCE(value, value, ...)`	The first value that is not blank. Takes two or more values.
`BLANK()`	A blank value.
`TRUE()`	True.
`FALSE()`	False.

Math and dates

Function	What it does
`DIVIDE(numerator, denominator)`	Division. Returns blank when the denominator is 0 or blank.
`ABS(number)`	Absolute value.
`ROUND(number, digits)`	Rounds to the given number of digits (a whole number from -18 to 18). Halves round away from zero.
`DATE(year, month, day)`	A date. Year, month and day must be whole numbers written in the expression, for example `DATE(2026, 1, 31)`.

Not available yet

These functions are not supported yet. If you use one, the check shows an error.

RANKX, ALLSELECTED, USERELATIONSHIP, CROSSFILTER, RELATEDTABLE, ISFILTERED, TOTALYTD, TOTALQTD, TOTALMTD, DATESYTD, DATESQTD, DATESMTD, SAMEPERIODLASTYEAR, DATEADD, DATESINPERIOD, PREVIOUSMONTH, PREVIOUSQUARTER, PREVIOUSYEAR, FORMAT, CONCATENATE, COUNTA, COUNTBLANK, IFERROR, MEDIAN, TODAY, NOW, YEAR, MONTH, DAY.

Limits

Item	Limit
Measures in a data model	2,000
Expression length	8,000 characters
Name	128 characters
Display folder and number format	256 characters each
Description	2,000 characters
Values in an `IN { ... }` list	1,000
Nesting depth	64 levels

Common errors

Message	What to do
A column must be inside an aggregate function	Wrap the column, for example `SUM(Orders[Amount])`.
This function only works inside CALCULATE	Move the function into `CALCULATE`.
Measures reference each other in a loop	Remove the loop between the measures.
Column not found	Check the table and column names.
Function not found	Check the spelling, or see Not available yet.
The expression is nested too deeply	Split the expression into several measures.

Common questions

Can a measure use another measure? Yes. Write the other measure’s name in brackets, for example [Total sales]. Measures cannot refer to each other in a loop.

Why does my measure show blank? Check that the data model is published and that the Data Tables have been refreshed. A measure tagged Needs rewrite always returns blank until you rewrite it.