Using CALCULATE Function DAX

  1. Home
  2. Using CALCULATE Function DAX

You can use the CALCULATE Function DAX for evaluating a table expression in a modified filter context. Also, it modifies the filter context applied to an expression that returns a scalar value.

DAXCopy – Syntax

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

Parameters

TermDefinition
expressionThe table expression to be evaluated.
filter1, filter2,…(Optional) Boolean expressions or table expressions that defines filters, or filter modifier functions.

Remember, the expression used as the first parameter must be a model table or a function that returns a table.

Types of Filters

  • Boolean filter expressions
  • Table filter expressions
  • Filter modification functions

Also, when there are multiple filters, they are evaluated by using the AND logical operator. That indicates all conditions must be TRUE at the same time.

Boolean filter expressions

A Boolean expression filter is an expression that evaluates to TRUE or FALSE. There are several rules that they must abide by:

  • They can reference only a single column.
  • They cannot reference measures.
  • They cannot use a nested CALCULATE function.
  • They cannot use functions that scan or return a table, including aggregation functions.

Table filter expression

The table expression filter implements a table object as a filter. This could be a reference to a model table, but it’s a function that returns a table object. Also, you can use the FILTER function to apply complex filter conditions, including those that cannot be defined by a Boolean filter expression.

Filter modifier functions

The filter modification functions permits you to do more than simply add filters. They provide you with additional control when modifying filter context.

FunctionObjectives
REMOVEFILTERSIt is used to remove all filters, or filters from one or more columns of a table, or from all columns of a single table.
ALL 1, ALLEXCEPT, ALLNOBLANKROWIt is used to remove filters from one or more columns, or from all columns of a single table.
KEEPFILTERSIt is used to add filter without removing existing filters on the same columns.
USERELATIONSHIPIt is used to engage an inactive relationship between related columns, in which case the active relationship will automatically become inactive.
CROSSFILTERIt is used to modify filter direction (from both to single, or from single to both) or disable a relationship.

For More visit: DAX avoid using FILTER

Microsoft Exam DA-100 Free Practice Test
Menu