top of page
  • Marek Vavrovic


Time intelligence functions

OPENINGBALANCEYEAR(<Expression>,<Dates> [,<Filter>][,<YearEndDate>])

Evaluates the specified expression for the date corresponding to the end of the previous year after applying specified filters.

OPENINGBALANCEQUARTER(<Expression>,<Dates> [,<Filter>])

Shifts the quarters and calculate the expression.

OPENINGBALANCEMONTH(<Expression>,<Dates> [,<Filter>])

Shifts the months and calculate the expression.

CLOSINGBALANCEYEAR(<Expression>,<Dates> [,<Filter>][,<YearEndDate>])

Returns the balance on the end of the year.

CLOSINGBALANCEQUARTER(<Expression>,<Dates> [,<Filter>])


Returns the end of quarter.

CLOSINGBALANCEMONTH(<Expression>,<Dates> [,<Filter>])

The expression evaluated at the last date of the month in the current context.


Returns the end of month.

Moves the given set of dates by a specified interval.

Returns the dates between two given dates.

Returns the dates from the given period.

Cumulative total in the month up to the last date.


Cumulative total.


Shifting the dates.


Shifting the dates.


Shift the date to the next day.


Returns the first value in the column for which the expression has a non blank value.


returns first non blank date.

returns last non blank date.


Cumulative total.


Cumulative total.


Returns the start of month.


Returns the start of year.


Returns a previous month.



Grouping and summarizing

GROUPBY (<table> [, <groupBy_columnName> [, <groupBy_columnName> [, …]]] [, <name>, <expression> [, <name>, <expression> [, …]]])

Creates a summary the input table grouped by the specified columns.

SUMMARIZE (<table>, <groupBy_columnName>[, <groupBy_columnName>]…[, <name>, <expression>]…)

Creates a summary of the input table grouped by the specified columns

SUMMARIZECOLUMNS( <groupBy_columnName> [, < groupBy_columnName >]…, [<filterTable>]…[, <name>, <expression>]…)

Returns a summary table over a set of groups.

Table manipulation functions

ROW(<name>, <expression>[[,<name>, <expression>]…])

Returns a table with a single row containing values that result from the expressions given to each column.

SELECTCOLUMNS(<table>, <name>, <scalar_expression>)

This function returns the selected columns from the source table.

ADDCOLUMNS(<table>, <name>, <expression>[, <name>, <expression>]…)

Adds calculated columns to the given table or table expression.

TREATAS(table_expression, <column>[, <column>[, <column>[,…]]]} )

Applies the result of a table expression as filters to columns from an unrelated table.

Filter functions

ALLSELECTED([<tableName> | <columnName>[, <columnName>[, <columnName>[,…]]]] )

Returns all the rows in a table except for those rows that are affected by the specified column filters. Restoring filters.


Modifies how filters are applied while evaluating a CALCULATE or CALCULATETABLE function.

Restoring filters.

ALL( [<table> | <column>[, <column>[, <column>[,…]]]] )

Returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied. Removing filters.

REMOVEFILTERS([<table> | <column>[, <column>[, <column>[,…]]]])

Clear filters from the specified tables or columns. Removing filters.


Removes all context filters in the table except filters that have been applied to the specified columns. Removing filters.


Clear all filters which are applied to a table. Removing filters.

ALLNOBLANKROW( {<table> | <column>[, <column>[, <column>[,…]]]} )

Returns all the rows except blank row in a table, or all the values in a column, ignoring any filters that might have been applied. Removing filters.

SELECTEDVALUE(<columnName>[, <alternateResult>])

Returns the value when there’s only one value in the specified column, otherwise returns the alternate result.

EARLIER(<column>, <number>)

Returns the value in the column prior to the specified number of table scans (default is 1).

CALCULATETABLE(<expression>[, <filter1> [, <filter2> [, …]]])

Evaluates a table expression in a context modified by filters.

Information functions

CONTAINS(<table>, <columnName>, <value>[, <columnName>, <value>]…)

Returns TRUE if there exists at least one row where all columns have specified values.

CONTAINSROW(<tableExpr>, <scalarExpr>[, <scalarExpr>, …])

Returns TRUE if a row of values exists or contained in a table, otherwise returns FALSE.

alternatively use KEEPFILTERS function, returns the same result

CONTAINSSTRING(<within_text>, <find_text>)

Returns TRUE or FALSE indicating whether one string contains another string.


HAONEFILTER returns TRUE when the number of directly filtered values on columnName is one; otherwise returns FALSE


HASONEVALUE if there is more than 1 filter applied this function returns FALSE. If you will use just 1 direct or just 1 indirect or just 1 cross filter, this function returns TRUE.


ISFILTERED returns TRUE when columnName is being filtered directly. If there is no filter on the column or if the filtering happens because a different column in the same table or in a related table is being filtered then the function returns FALSE.


ISCROSSFILTERED returns TRUE when columnName or another column in the same or related table is being filtered.


return type : Boolean


Checks if a table is empty.


Checks whether a value is an error, and returns TRUE or FALSE.

59 views0 comments

Recent Posts

See All


bottom of page