• Marek Vavrovic

Index

Information functions

Filter functions

Table manipulation functions

Grouping and summarizing

Time intelligence functions


OPENINGBALANCEYEAR

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

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

Shifts the quarters and calculate the expression.


OPENINGBALANCEMONTH

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

Shifts the months and calculate the expression.


CLOSINGBALANCEYEAR

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

Returns the balance on the end of the year.


CLOSINGBALANCEQUARTER

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


ENDOFQUATER

ENDOFQUARTER('Date'[date])

Returns the end of quarter.


CLOSINGBALANCEMONTH

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

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


ENDOFMONTH

ENDOFMONTH('Date'[date])

Returns the end of month.


DATEADD

Moves the given set of dates by a specified interval.


DATESBETWEEN

Returns the dates between two given dates.


DATESINPERIOD

Returns the dates from the given period.


DATESMTD

Cumulative total in the month up to the last date.


DATESYTD

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

Cumulative total.


PARALLELPERIOD

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

Shifting the dates.


SAMEPERIODLASTYEAR

SAMEPERIODLASTYEAR(<dates>)

Shifting the dates.


NEXTDAY

NEXTDAY(<dates>)

Shift the date to the next day.


NEXTMONTH


NEXTQUARTER


NEXTYEAR


FIRSTNONBLANK

FIRSTNONBLANK(<column>,<expression>)

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


LASTNONBLANK

LASTNONBLANK(<column>,<expression>)


FIRSTDATE

returns first non blank date.


LASTDATE

returns last non blank date.


TOTALMTD

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

Cumulative total.


TOTALYTD

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

Cumulative total.


STARTOFMONTH

STARTOFMONTH(<dates>)

Returns the start of month.


STARTOFYEAR

STARTOFYEAR(<dates>)

Returns the start of year.


PREVIOUSMONTH

PREVIOUSMONTH(<dates>)

Returns a previous month.


PREVIOUSYEAR

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


PREVIOUSDAY

PREVIOUSDAY(<dates>)


Grouping and summarizing


GROUPBY

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

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


SUMMARIZE

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

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


SUMMARIZECOLUMNS

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

Returns a summary table over a set of groups.


Table manipulation functions


ROW

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

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


SELECTCOLUMNS

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

This function returns the selected columns from the source table.

AddColumns

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

Adds calculated columns to the given table or table expression.


TREATAS

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

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


Filter functions


ALLSELECTED

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

KEEPFILTERS(<expression>)

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

Restoring filters.


ALL

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

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

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


ALLEXCEPT

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

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


ALLCROSSFILTERED

ALLCROSSFILTERED(<table>)

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


ALLNOBLANKROW

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

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

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


EARLIER

EARLIER(<column>, <number>)

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


CALCULATETABLE

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

Evaluates a table expression in a context modified by filters.


Information functions


CONTAINS

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

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


CONTAINSROW

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

CONTAINSSTRING(<within_text>, <find_text>)

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


HASONEFILTER

HASONEFILTER(<columnName>)

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


HASONEVALUE

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

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

ISCROSSFILTERED(<columnName>)

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


ISBLANK

ISBLANK(<value>)

return type : Boolean


ISEMPTY

ISEMPTY(<table_expression>)

Checks if a table is empty.


ISERROR

ISERROR(<value>)

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



6 views0 comments

Recent Posts

See All