Index
- Marek Vavrovic
- Oct 5, 2021
- 3 min read
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.
Comments