top of page
Marek Vavrovic

Index

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>])


ENDOFQUARTER('Date'[date])

Returns the end of quarter.


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

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


ENDOFMONTH('Date'[date])

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.


DATESYTD(<Dates>[,<YearEndDate>])

Cumulative total.


PARALLELPERIOD(<dates>,<number_of_intervals>,<interval>)

Shifting the dates.


SAMEPERIODLASTYEAR(<dates>)

Shifting the dates.


NEXTDAY(<dates>)

Shift the date to the next day.





FIRSTNONBLANK(<column>,<expression>)

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


LASTNONBLANK(<column>,<expression>)


returns first non blank date.


returns last non blank date.


TOTALMTD(<expression>,<dates>[,<filter>])

Cumulative total.


TOTALYTD(<expression>,<dates>[,<filter>][,<year_end_date>])

Cumulative total.


STARTOFMONTH(<dates>)

Returns the start of month.


STARTOFYEAR(<dates>)

Returns the start of year.


PREVIOUSMONTH(<dates>)

Returns a previous month.


PREVIOUSYEAR(<dates>[,<year_end_date>])


PREVIOUSDAY(<dates>)


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.


KEEPFILTERS(<expression>)

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.


ALLEXCEPT(<table>,<column>[,<column>[,…]])

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


ALLCROSSFILTERED(<table>)

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.


HASONEFILTER(<columnName>)

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


HASONEVALUE(<columnName>)

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(<columnName>)

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(<columnName>)

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


ISBLANK(<value>)

return type : Boolean


ISEMPTY(<table_expression>)

Checks if a table is empty.


ISERROR(<value>)

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



63 views0 comments

Recent Posts

See All

Comments


bottom of page