Filter Functions

Question 1

ALLCROSSFILTERED(table)

Answer 1

Clear all filters which are applied to a table.
Can only be used to clear filters but not to return a table. Can be used only as a CALCULATE modifier and cannot be used as a table function.
This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

Question 2

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

Answer 2

Clear filters from the specified tables or columns.

Question 3

PARTITIONBY ( partitionBy_columnName[, partitionBy_columnName [, …] ] )

Answer 3

Defines the columns that are used to partition a window function’s relation parameter.

Question 4

ORDERBY ( orderBy_columnName[, order][, orderBy_columnName [, order]] [, …] )

Answer 4

Defines the columns that determine the sort order within each of a window function’s partitions.

Question 5

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

Answer 5

Evaluates a table expression in a modified filter context.

Question 6

CALCULATE(expression[, filter1 [, filter2 [, …]]])

Answer 6

Evaluates an expression in a modified filter context.

Question 7

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

Answer 7

From the parent table of a relationship, returns all rows but the blank row, or all distinct values of a column but the blank row, and disregards any context filters that might exist.

Question 8

KEEPFILTERS(expression)

Answer 8

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

Question 9

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

Answer 9

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

Question 10

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

Answer 10

Removes context filters from columns and rows in the current query, while retaining all other context filters or explicit filters.

Question 11

INDEX(position[, relation][, orderBy][, blanks][, partitionBy])

Answer 11

Returns a row at an absolute position, specified by the position parameter, within the specified partition, sorted by the specified order. If the current partition can’t be deduced to a single partition, multiple rows may be returned.

Question 12

OFFSET ( delta[, relation][, orderBy][, blanks][, partitionBy] )

Answer 12

Returns a single row that is positioned either before or after the current row within the same table, by a given offset. If the current row cannot be deduced to a single row, multiple rows may be returned.

Question 13

FILTER(table,filter)

Answer 13

Returns a table that represents a subset of another table or expression.

Question 14

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

Answer 14

Returns all the rows in a table, or all the values in a column, ignoring any filters that might have been applied. This function is useful for clearing filters and creating calculations on all the rows in a table.

Question 15

WINDOW ( from[, from_type], to[, to_type][, relation][, orderBy][, blanks][, partitionBy] )

Answer 15

Returns multiple rows which are positioned within the given interval.

Question 16

EARLIER(column, number)

Answer 16

Returns the current value of the specified column in an outer evaluation pass of the mentioned column.

Question 17

EARLIEST(column)

Answer 17

Returns the current value of the specified column in an outer evaluation pass of the specified column.

Question 18

LOOKUPVALUE(
result_columnName,
search_columnName,
search_value
[, search2_columnName, search2_value]…
[, alternateResult]
)

Answer 18

Returns the value for the row that meets all criteria specified by one or more search conditions.

Question 19

SELECTEDVALUE(columnName[, alternateResult])

Answer 19

Returns the value when the context for columnName has been filtered down to one distinct value only. Otherwise returns alternateResult.